x

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)
more ▼

asked Jul 28, 2010 at 05:33 AM in Default

MGreendyk gravatar image

MGreendyk
1 1 2 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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
more ▼

answered Jul 28, 2010 at 05:55 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

@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, 2010 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, 2010 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, 2010 at 01:14 PM MGreendyk
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jul 28, 2010 at 05:43 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 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, 2010 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, 2010 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, 2010 at 07:30 AM Oleg
@Oleg - yes, for sure :)
Jul 28, 2010 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, 2010 at 12:46 PM MGreendyk
(comments are locked)
10|1200 characters needed characters left
FEA8CC6C52430366049BB7AD893CF736697A90F127B67581927359A8762D825A2E4305C357B89F3E4491F0A99AFED9DF2C915D825DF9F4FCCF54BA4C2DE3B1BA78CB5B18EEBA55063C154C32F570BF357CDF29C27A649986D1B33E1914CCA6D6B34C009C3585BC6146D97164CA94AF83465E582D9C8636853B7F05ADA881BE861E6F0B33C5F19991B069CC8377B6273D1BE06B7C7683B39AA36431BAEED49E63FE89D6DE67
more ▼

answered May 01, 2012 at 06:33 AM

marlen gravatar image

marlen
0

(comments are locked)
10|1200 characters needed characters left
Vs.passReport.lock
more ▼

answered May 01, 2012 at 06:38 AM

marlen gravatar image

marlen
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1943
x984
x369

asked: Jul 28, 2010 at 05:33 AM

Seen: 2350 times

Last Updated: May 01, 2012 at 06:38 AM