Pass report paramter to dynamic sql

Question

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)

Answer 1

0

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

answered Jul 28 '10 at 05:55 AM

Cyborg
10.1k ● 29 ● 39 ● 44

@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 :)

Jul 28 '10 at 07:38 AM Oleg

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!

Aug 06 '10 at 11:19 AM MGreendyk

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'.

Aug 06 '10 at 01:14 PM MGreendyk

(comments are locked)

Answer 2

0

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.

answered Jul 28 '10 at 05:43 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

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 ' ===========================

Jul 28 '10 at 06:15 AM MGreendyk

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

Jul 28 '10 at 06:22 AM Matt Whitfield ♦♦

@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.

Jul 28 '10 at 07:30 AM Oleg

@Oleg - yes, for sure :)

Jul 28 '10 at 07:40 AM Matt Whitfield ♦♦

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!

Aug 06 '10 at 12:46 PM MGreendyk

(comments are locked)

Answer 3

0

FEA8CC6C52430366049BB7AD893CF736697A90F127B67581927359A8762D825A2E4305C357B89F3E4491F0A99AFED9DF2C915D825DF9F4FCCF54BA4C2DE3B1BA78CB5B18EEBA55063C154C32F570BF357CDF29C27A649986D1B33E1914CCA6D6B34C009C3585BC6146D97164CA94AF83465E582D9C8636853B7F05ADA881BE861E6F0B33C5F19991B069CC8377B6273D1BE06B7C7683B39AA36431BAEED49E63FE89D6DE67

answered May 01 '12 at 06:33 AM

marlen
0

(comments are locked)

Answer 4

0

Vs.passReport.lock

answered May 01 '12 at 06:38 AM

marlen
0

(comments are locked)

Pass report paramter to dynamic sql

Follow this question

Related Questions