asked
Building Dynamic SQL Insert Statement
Hi there, I'm trying to create and execute dynamic sql insert statements in TSQL2000 using the following code: DECLARE @number VARCHAR(100) DECLARE @name VARCHAR(100) DECLARE @sql VARCHAR(1000) SET @number = '141,162' SET @name = 'MR TEST' SET @sql = 'SELECT ''INSERT INTO STAFF(NAME,NUMBER) SELECT '''''' + @name + '''''','' + Value FROM dbo.FnSplit(@number,'','') ' EXEC sp_executesql (@sql) It keeps failing with "Line 9: Incorrect syntax near '@sql'." but so far I've been unable to resolve the issue. Basically I'm trying the dynamically execute statements at runtime similar to: INSERT INTO STAFF(NAME,NUMBER) SELECT 'MR TEST',141 INSERT INTO STAFF(NAME,NUMBER) SELECT 'MR TEST',162 TIA
While debugging this, instead of trying to execute the @sql variable, print it out and paste it into SSMS so you can let the engine parse it. That way you can see the SQL it is generating and make adjustments until it's right. Once that part is done, you can add the sp_executesql back in
a +1 for @KenJ's suggestion. Judging by the syntax colouring above, it looks as though there are a couple of issues with your `SET @sql` statement.
[sp_executesql][1] doesn't expect the brackets. The clue is the error message which references the variable - not the content of the variable. Also - it should be an NVARCHAR, not a VARCHAR. so that line should read EXEC sp_executesql @statement=@sql Note - I think you also have something wrong with your SET @SQL= line, suggest you print @SQL and debug that too. [1]:
https://msdn.microsoft.com/en-GB/library/ms188001.aspx
Thanks for everyones suggestions. Debugging by printing out the variable and also changing the EXEC sp_executesql statement resolved my issue. Solution was: DECLARE @number NVARCHAR(100) DECLARE @name NVARCHAR(100) DECLARE @sql NVARCHAR(1000) SET @number = '141,162' SET @name = 'MR TEST' SET @sql = 'INSERT INTO STAFF(NAME,NUMBER) SELECT ''' + @name + ''', Value FROM dbo.FnSplit(''' + @number + ''','','') ' EXEC sp_executesql @statement=@sql Many thanks everyone.
Update - link to SQL 2000 MSDN page on sp_executesql, as that's what you specified you are using -
https://technet.microsoft.com/en-us/library/aa933299(v=sql.80).aspx
In the statement for SET @sql = .... Try removing 'SELECT' and remove a single quote from each side of + @name +
20 People are following this question.
