question

dhivyabr avatar image
dhivyabr asked

Must declare the scalar variable @edate

Hi All, I need some help with this error. I get an error asking me to declare the scalar variable although I've already declared it on my stored proc. declare edate date declare @query nvarchar(max) set @query =( (select P1 from dbo.parameters where edate = ) + '@edate' ) CREATE TABLE #temp1 (record integer) insert #temp1 exec(@query) Thanks in advance.
stored-proceduresparameterserror-message
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.

ThomasRushton avatar image
ThomasRushton answered
First: declare @edate date second: [variable scope][1]. What this means is that your variable might not be available where you're planning on using it. In this case, you're passing a parameterised t-SQL statement into an `EXEC` statement, which is, in effect, the equivalent of launching a new separate query in a separate query space. Anything you want to use in the EXEC'd statement has to be declared *in* that statement. [1]: https://www.google.co.uk/search?q=variable+scope+t-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.

Oleg avatar image
Oleg answered
@ThomasRushton already answered, but I would like to expand on his answer to point out what else needs attention. 1. The @query variable is not built correctly, it needs to be a string, and appears to be missing the single quotes around the select statement 2. The @date variable must be also set to some value, not just declared. Otherwise, if the variable is not set then it is null, and because concat of nulls yields null by default, the @query will end up as null as well. 3. exec should call sp_executesql rather than just the string which contains the script. This is because the script has a parameter, so the plain exec is not suitable for it. To make the long story short, the snippet in question needs to look like this: declare @edate date; declare @query nvarchar(max); select @edate = '20160815', -- the variable needs to be set to some value @query = N'select P1 from dbo.parameters where edate = @edate'; create table #temp1 (record integer); insert into #temp1 exec sp_executesql @stmt = @query, -- parameterized query to execute @params = N'@edateParam date', -- parameter definition @edateParam = @edate; -- parameter value from the outside script Please have a look at the way the query is defined. It does not actually append the value of the parameter to the script, but is merely defining the script which needs a parameter. This necessitates the use of the sp\_executesql so the parameter can be passed into query. Doing so it much safer than to pad the string to be executed with actual parameter values before hand. In other words, the script can be restated to not even use the parameter passed into the sp\_executesql, but use the actual @edate value already appended to it, but this leaves the door open to the SQL injection attacks. Hope this helps, Oleg
1 comment
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.

Thanks Thomas and Oleg. Yes, that helps. I'll have to use temp tables every time I use an out-of-scope parameter in my query.
0 Likes 0 ·

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.