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.
First: declare @edate date second: [variable scope]. 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. :
@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