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)
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.
@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.
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 ' ===========================
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`
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!
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
@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 :)
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!
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'. ------------------------------
FEA8CC6C52430366049BB7AD893CF736697A90F127B67581927359A8762D825A2E4305C357B89F3E4491F0A99AFED9DF2C915D825DF9F4FCCF54BA4C2DE3B1BA78CB5B18EEBA55063C154C32F570BF357CDF29C27A649986D1B33E1914CCA6D6B34C009C3585BC6146D97164CA94AF83465E582D9C8636853B7F05ADA881BE861E6F0B33C5F19991B069CC8377B6273D1BE06B7C7683B39AA36431BAEED49E63FE89D6DE67
Vs.passReport.lock
1 Person is following this question.
