question

decreale avatar image
decreale asked

How to pass a parameter to Dynamic SQL

I have the following code that works with a stored procedure with no parameters. When I use a stored procedure with parameters I'm not sure where to put the parameters for the code to work declare @bdate as date ='2015-01-01' declare @edate as date = '2015-12-31' DECLARE @sql nvarchar(4000); DECLARE @BiSQL nvarchar(4000); DECLARE @dName varchar(100); SET @dName = 'XX'; SET @sql = 'usp_ME_Test'; SET @BiSQL = 'USE ' + @dName + '; EXEC sp_executesql N''' + @sql + ''''; EXEC (@BiSQL)
dynamic sql
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.

1 Answer

· Write an Answer
Gazz avatar image
Gazz answered
If you use the Format SQL button it makes it a lot easier to read: DECLARE @bdate AS DATE = '2015-01-01' DECLARE @edate AS DATE = '2015-12-31' DECLARE @sql NVARCHAR(4000); DECLARE @BiSQL NVARCHAR(4000); DECLARE @dName VARCHAR(100); SET @dName = 'XX'; SET @sql = 'usp_ME_Test'; SET @BiSQL = 'USE ' + @dName + '; EXEC sp_executesql N''' + @sql + ''''; EXEC (@BiSQL)
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.