question

MGreendyk avatar image
MGreendyk asked

Pass report paramter to dynamic sql

Hello.. I've used a prior post's instructions to attempt passing a report's parameter - @From_Date and am getting a 'Must declare the scalar variable @From_Date' error message -- I don't know where to go from here! Any ideas.. Thanks in advance! DECLARE @SQLAI nvarchar(max) SET @SQLAI = 'DECLARE @FROM datetime' SET @SQLAI = @SQLAI + ' SET @FROM = @From_Date ' SET @SQLAI = @SQLAI + ' SELECT @FROM' EXEC (@SQLAI)
sql-server-2005t-sqlquery
10 |1200

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

Matt Whitfield avatar image
Matt Whitfield answered
When you EXEC() something, the variables that you are working with aren't visible to the query that you are EXEC()ing. They are not in the same scope. Instead of building the string with `+ ' SET @FROM = @From_Date '` try something like `+ ' SET @FROM = ' + CONVERT(varchar, @From_Date, 121) + ' '` This will build your dynamic query with the value that is in the variable, rather than a reference to the variable. I am assuming here that @From_Date is a date/time.
5 comments
10 |1200

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

Oleg avatar image Oleg commented ·
@Matt Whitfield +1 This is a good solution. I also like the one offered by @Cyborg because it is clean and bulletproof. If the variable in question were be a varchar and had single quotes in it then building dynamic "parameterless" in the end SQL would also require special handling of those. The sp_executesql is robust enough to accept parameters both in and out and as many as needed.
1 Like 1 ·
MGreendyk avatar image MGreendyk commented ·
OK... will try this. Thanks! My next question will be : how would I add a @TO datetime in addition to this? And, how would I incorporate this into the entire select below: ============== DECLARE @SQLAcctInq nvarchar(max) SET @SQLAcctInq = ' /* Inserting data into the Inq table for Account inquiries*/ Insert #Activity SELECT FilteredAccount.accountid, FilteredAccount.name, FilteredAccount.new_accountmanagerid, FilteredAccount.new_accountmanageridname, FilteredIncident.new_completiondate, ''I'' as Type, 1 as Inq_ctr, 0 as InqNote_ctr, 0 as AD_ctr, 0 as ADNote_ctr, FilteredIncident.new_originalbilledmemberresponsibili, FilteredIncident.new_finalmemberresponsiblity, FilteredIncident.new_originalbilledmemberresponsibili - FilteredIncident.new_finalmemberresponsiblity AS SAVINGS FROM FilteredIncident INNER JOIN FilteredAccount ON FilteredIncident.customerid = FilteredAccount.accountid ' ===========================
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Just the same way, `SET @TO = ' + CONVERT(varchar, @To_Date, 121) + ' '` You would want to incorporate it into your query with a WHERE clause - `WHERE FilteredIncident.new_completiondate BETWEEN @FROM AND @TO`
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Oleg - yes, for sure :)
0 Likes 0 ·
MGreendyk avatar image MGreendyk commented ·
Hi there... thanks for your responses. Admittedly, I'm struggling here with several things.. 1.) The @FROM date that is being returned when I just execute @SQLAI is always the year 1900... from the first response above... 2.) I'm completely lost as to where I add these statements. When I begin with them, the only result I get is the date and nothing else 'seems to' execute. I really appreciate your help!
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
Try this DECLARE @FROM_DATE DATETIME, @SQLAI nVARCHAR(MAX), @Param nvarchar(100), @From DATETIME SET @FROM_DATE = GETDATE() SET @SQLAI = N'SELECT @pFROM = @PFromDate' SET @Param = N'@PFromDate DATETIME,@pFROM DATETIME OUTPUT'; EXEC sp_executesql @SQLAI, @Param, @PFromDate = @From_Date,@PFrom = @FROM OUTPUT SELECT @From
3 comments
10 |1200

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

Oleg avatar image Oleg commented ·
@Cyborg +1 This is a very clean bulletproof solution, but it would be even better if the casing of the parameter names was consistent (**@FROM_DATE** will not be understood by **@PFromDate = @From_Date** part if the database is configured to use any case sensitive collation). In the past I never cared about consisted casing, but there were enough recent posts by Hugo Kornelis and others on the main site to make me reconsider my opinion :)
1 Like 1 ·
MGreendyk avatar image MGreendyk commented ·
Hi there... thanks for your responses. Admittedly, I'm struggling here with several things.. 1.) The @FROM date that is being returned when I just execute @SQLAI is always the year 1900... This is from the first response above... 2.) I'm completely lost as to where I add these statements. When I begin with them, the only result I get is the date and nothing else 'seems to' execute. I really appreciate your help!
0 Likes 0 ·
MGreendyk avatar image MGreendyk commented ·
Here's what I've entered: DECLARE @From_Date datetime, @SQLAI nvarchar(max), @Param nvarchar(100), @FROM datetime SET @From_Date = GETDATE(); SET @SQLAI = N'SELECT @pFROM = @PFromDate'; SET @Param = N'@PFromDate datetime, @pFROM datetime OUTPUT'; EXEC sp_executesql @SQLAI, @Param, @PFromDate = @From_Date, @PFrom = @FROM OUTPUT SELECT @FROM Any idea why I might be getting this error message? An error occurred while executing the query. The variable name '@FROM' has already been declared. Variable names must be unique within a query batch or stored procedure. Incorrect syntax near ' SELECT @pFROM = @PFromDate'. Incorrect syntax near ' @PFromDate datetime, @pFROM datetime OUTPUT'. ------------------------------
0 Likes 0 ·
marlen avatar image
marlen answered
FEA8CC6C52430366049BB7AD893CF736697A90F127B67581927359A8762D825A2E4305C357B89F3E4491F0A99AFED9DF2C915D825DF9F4FCCF54BA4C2DE3B1BA78CB5B18EEBA55063C154C32F570BF357CDF29C27A649986D1B33E1914CCA6D6B34C009C3585BC6146D97164CA94AF83465E582D9C8636853B7F05ADA881BE861E6F0B33C5F19991B069CC8377B6273D1BE06B7C7683B39AA36431BAEED49E63FE89D6DE67
10 |1200

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

marlen avatar image
marlen answered
Vs.passReport.lock
10 |1200

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.