You are at this post because you have created a variable in a TSql script and would like to assign it a value. You would like to assign this value dynamically to this SQL Server Variable. For example, perhaps you want to assign the average price of a customer purchase to a variable, or you would like to assign the count of all the rows in a particular query to an SQL variable so that you can use it later in the script.
Below is an example on how to count all the rows in a Products table, assign it to a query named @ProductCount and then print the results to our SQL Server Management Studio Message tab (via the print statement – optional). As always, if you have any questions, please don’t hesitate to post your questions or comments.
This is however fine, I want to do this dynamically by using exec on dynamically built SQL string. The example and the error is follows:
Using adventureworks on SQL server 2005
declare @sql nvarchar(max);
set @sql = null;
select @sql = ‘Begin’ + CHAR(10);
select @sql = @sql +’declare @num bigint;’ + CHAR(10);
select @sql = @sql + ‘Select @num = department_id from HumanResources.Department where name =’ + ”’Engineering”’ + ‘;’+ CHAR(10);
select @sql = @sql + ‘End;’ + char(10);
print @sql
exec @sql
on executing the string, I get the following error
Msg 2812, Level 16, State 62, Line 8
Could not find stored procedure ‘declare @num bigint;
Select @num = department_id from HumanResources.Department where name =’Engineering’;
‘.
Kindly advise.
There are a couple of things that I would change in your query. First there are two things in the the fifth line
1.) Line 5:
department_id–> may need to be changed to –>DepartmentId(It’s like that in my table)2.) Line 5:
namemay need to be enclosed in brackets i.e.[name]3.) Line 8:
exec @sqlshould readexec(@sql)So your code should be something like:
declare @sql nvarchar(max);set @sql = null;
select @sql = 'Begin' + CHAR(10);
select @sql = @sql +'declare @num bigint;' + CHAR(10);
select @sql = @sql + 'Select @num = DepartmentID from HumanResources.Department where [Name] =' + '''Engineering''' + ';'+ CHAR(10);
select @sql = @sql + 'End;' + char(10);
print @sql
exec (@sql)
Hope that’s what you are needing.
Thanks, the code string executed fine, however I am curious to know as to how @sql enclosed in brackets worked fine and the same goes with name coulmn as well.
Glad to hear it worked out.
I suggested the ‘name’ column to be enclosed in brackets because I think it’s a keyword (similar to a reserved word in other programming languages) and without the brackets, you may get an error.
Regarding the parentheses surrounding @sql, I think exec() is a function that requires parentheses. So that may just be part of the syntax.