November 23, 2010 at 4:28 am
Being @sql a NVARCHAR(2000), and @nameDB a VARCHAR(50), I'm trying to run these dynamic SQL commands (copy pasted):
SET @sql='INSERT INTO @EventList
([EventID],..... <long list of fields here>)
SELECT [EventID], ............... <same long list of fields here>
FROM ' + @nameDB + '.dbo.tblEvent'
+ 'WHERE VolunteerID = @VolunteerID
ORDER BY VolunteerID,
ReportedDate DESC'
EXEC(@sql)
but I get an error that's probably related with the way my @sql variable is built (1st time I do this). What am I doing wrong (spaces, line carriage, combining INSERT & SELECT in the same sentence.....) ?
Thanks in advance, Al
November 23, 2010 at 4:35 am
BTW, the error is 443: 'Invalid use of side-effecting or time-dependent operator in the EXEC command'
November 23, 2010 at 6:28 am
I am a junior developer, so my answer may not be great, because I too am still learning (hence posting under SQL Server Newbies). I hope this helps.
Assuming @EventList is a table already in your db, you really do not need to declare a parameter such as @sql. You should be able to just execute it as a stored procedure. However, what is @nameDB for? And it seems you need to declare the parameter @VolunteerID.
Also, from what I gather, you are trying to:
INSERT INTO @EventList (bunch of fields) that are SELECT bunch of fields
FROM table containing specific text where IDfield = variable
If that is correct, then you could try the below code. May lead you in the right direction. Otherwise, it would seem as if you want to create a cursor, and execute the statements from a string. Which, I suggest doing some research on cursors, very neat stuff. Always research a lot, and test on test databases first.
Otherwise, you could try:
CREATE PROCEDURE dbo.usp_InsertNameOfProcedure
@VolunteerID nvarchar(255) --parameter to be passed in
AS
BEGIN
SET NOCOUNT ON
DECLARE @nameDB nvarchar(255) --if declaring this, set some value to it (this is the variable)
INSERT INTO @EventList ([EventID],..... <long list of fields here>)
SELECT [EventID], ............... <same long list of fields here>
FROM '%@nameDB%' + '.dbo.tblEvent' --lose the + signs. use %
WHERE VolunteerID = @VolunteerID --lose the + sign, no need
ORDER BY VolunteerID, ReportedDate DESC'
--below will execute the procedure in sqlserver, or use UI code to provide the variable
USE nameofdatabase
GO
EXEC dbo.usp_InsertNameOfProcedure @VolunteerID ='some value'
GO
November 23, 2010 at 6:37 am
a_ud (11/23/2010)
Being @sql a NVARCHAR(2000), and @nameDB a VARCHAR(50), I'm trying to run these dynamic SQL commands (copy pasted):SET @sql='INSERT INTO @EventList
([EventID],..... <long list of fields here>)
SELECT [EventID], ............... <same long list of fields here>
FROM ' + @nameDB + '.dbo.tblEvent'
+ 'WHERE VolunteerID = @VolunteerID
ORDER BY VolunteerID,
ReportedDate DESC'
EXEC(@sql)
but I get an error that's probably related with the way my @sql variable is built (1st time I do this). What am I doing wrong (spaces, line carriage, combining INSERT & SELECT in the same sentence.....) ?
Thanks in advance, Al
yes, the @volunteerID needs to become ' + @VolunteerID + '
How many columns do you have? Are you hitting the 2000 character declared limit?
November 23, 2010 at 6:59 am
Watch the scope of your @Events table variable:
DECLARE @Events TABLE ([Name] varchar(120))
DECLARE @Id INT, @sql VARCHAR(2000)
SET @Id = 3
SET @sql = 'SELECT TOP 10 [Name] ' + 'FROM dbo.syscolumns ' + 'WHERE ID = ' + CAST(@Id AS VARCHAR(5))
INSERT INTO @Events ([Name]) EXEC(@Sql)
SELECT [Name] FROM @Events
-- 10 rows returned
SET @sql = 'INSERT INTO @Events ([Name]) SELECT [Name] ' + 'FROM dbo.syscolumns ' + 'WHERE ID = ' + CAST(@Id AS VARCHAR(5))
EXEC(@Sql)
-- Msg 1087, Level 15, State 2, Line 1
-- Must declare the table variable "@Events".
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 23, 2010 at 7:32 am
Hi guys,
Thanks to all, but I'm still struggling to see what the problem is (and that would really help). Structures like this:
set @sql=<whatever>
exec(@sql)
usually work in dynamic SQL (I learnt this yesterday). Now, I have this one:
************
SET @sql='INSERT INTO @EventList
([EventID],[VolunteerID],[ReportedDate]
SELECT [EventID], [VolunteerID],[ReportedDate]
FROM ' + @nameDB + '.dbo.tblEvent'
+ 'WHERE VolunteerID = ' + @VolunteerID+
'ORDER BY VolunteerID,ReportedDate DESC'
EXEC(@sql)
**************
that doesn't work and gives error 443 (see above). @nameDB is the name of a database (a different one), passed as a parameter, and set up previously. Both marks ' ' have been added to the variable @VolunteerID.
This should work, right? But it doesn't....
November 23, 2010 at 7:34 am
grahamc (11/23/2010)
a_ud (11/23/2010)
Being @sql a NVARCHAR(2000), and @nameDB a VARCHAR(50), I'm trying to run these dynamic SQL commands (copy pasted):SET @sql='INSERT INTO @EventList
([EventID],..... <long list of fields here>)
SELECT [EventID], ............... <same long list of fields here>
FROM ' + @nameDB + '.dbo.tblEvent'
+ 'WHERE VolunteerID = @VolunteerID
ORDER BY VolunteerID,
ReportedDate DESC'
EXEC(@sql)
but I get an error that's probably related with the way my @sql variable is built (1st time I do this). What am I doing wrong (spaces, line carriage, combining INSERT & SELECT in the same sentence.....) ?
Thanks in advance, Al
yes, the @volunteerID needs to become ' + @VolunteerID + '
How many columns do you have? Are you hitting the 2000 character declared limit?
good point, missed that one
November 23, 2010 at 7:36 am
a_ud (11/23/2010)
Hi guys,Thanks to all, but I'm still struggling to see what the problem is (and that would really help). Structures like this:
set @sql=<whatever>
exec(@sql)
usually work in dynamic SQL (I learnt this yesterday). Now, I have this one:
************
SET @sql='INSERT INTO @EventList
([EventID],[VolunteerID],[ReportedDate]
SELECT [EventID], [VolunteerID],[ReportedDate]
FROM ' + @nameDB + '.dbo.tblEvent'
+ 'WHERE VolunteerID = ' + @VolunteerID+
'ORDER BY VolunteerID,ReportedDate DESC'
EXEC(@sql)
**************
that doesn't work and gives error 443 (see above). @nameDB is the name of a database (a different one), passed as a parameter, and set up previously. Both marks ' ' have been added to the variable @VolunteerID.
This should work, right? But it doesn't....
So what do you get when you PRINT @sql ?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 23, 2010 at 8:18 am
I can't run the instruction PRINT (@SQL) on a separate query. The reason for this is that I can't reproduce (or don't know how) the values of the @eventsList variable.
November 23, 2010 at 8:32 am
a_ud (11/23/2010)
I can't run the instruction PRINT (@SQL) on a separate query. The reason for this is that I can't reproduce (or don't know how) the values of the @eventsList variable.
If you can't issue
PRINT @sql
before
EXEC(@sql)
then there's something critical which you haven't yet mentioned.
Where is @eventsList variable declared? As what type?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 23, 2010 at 9:32 am
Hi Chris,
@eventsList is a TABLE type with several fields. It is defined as the RETURNS value of a function (all this comes from a function. The final goal is to replace a hard-code, the name of the database, to then create a master-template for future projects where the dabtabases will have different names).
Inside the procedure there's the SQL sentence (INSERT + SELECT + FROM ' + @nameDB +'etc-etc) that you've seen.
feel free to send more questions.
November 23, 2010 at 9:41 am
a_ud (11/23/2010)
Hi Chris,@eventsList is a TABLE type with several fields. It is defined as the RETURNS value of a function (all this comes from a function. The final goal is to replace a hard-code, the name of the database, to then create a master-template for future projects where the dabtabases will have different names).
Inside the procedure there's the SQL sentence (INSERT + SELECT + FROM ' + @nameDB +'etc-etc) that you've seen.
feel free to send more questions.
You can't use dynamic sql in a function, you will probably have to rethink this as a stored procedure.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 23, 2010 at 9:59 am
Are you sure? The problem seemed easy enough: we have 10 table value functions (multistated) with many hard-coded references to 2 databases. It's as easy as replacing the names of these 2 databases in 2 different generic cases:
Case 1: I use a string variable that stores the value of the database, it pulls it from a 'normal' table. Works fine in all functions.
Case 2: replacing the name of these 2 databases in INSERTs, SQLs sentences and so on. Apparently (I'm no expert!) this can only be done using 'dynamic SQL', that is, re-building the whole SQL/INSERT-etc in a VARCHAR string and then executing it.
But now you say that's not possible. Who is right? Al 🙂
November 23, 2010 at 9:59 am
a_ud (11/23/2010)
Hi Chris,@eventsList is a TABLE type with several fields. It is defined as the RETURNS value of a function (all this comes from a function. The final goal is to replace a hard-code, the name of the database, to then create a master-template for future projects where the dabtabases will have different names).
Inside the procedure there's the SQL sentence (INSERT + SELECT + FROM ' + @nameDB +'etc-etc) that you've seen.
feel free to send more questions.
This earlier post by Lowell may help you.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply