question

David 2 1 avatar image
David 2 1 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
sql-server-2000tsqlinsertdynamic-sql
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
1 Like 1 ·
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.
0 Likes 0 ·
Great idea. Thanks.
0 Likes 0 ·
Dave_Green avatar image
Dave_Green answered
[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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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
0 Likes 0 ·
Alvin Ramard avatar image
Alvin Ramard answered
In the statement for SET @sql = .... Try removing 'SELECT' and remove a single quote from each side of + @name +
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.