Assign exec execution results to variable output in SQL Server stored procedure

Assign exec execution results to variable output in the original SQL Server stored procedure

Background:

In case of a situation where the table name and some properties of the table are passed to the stored procedure dynamically (the primary key ID is used here), then the two variables are used to isolate some of the field values of the data, and then the values are used for the logical operation (their own logic), and the result is finally output. Speak not much, directly, to see if it is the result you want:

Description: "Zone 1 is the table in which you want to test" "Area 2 is the data in the table" "Area 3 is the Jan+feb+mar column value for the data in the table and 5"

The stored procedure code is as follows:

It can be seen that since it is possible to get the values of the Jan, the Feb, and the Mar, it is easy to make your own judgments back. Don't dwell on it.

Because the apprenticeship is not fine, the above conclusions are inspired by others, related links: http://www.cnblogs.com/wanyuan8/archive/2011/11/09/2243483.html

Post the original author Boven (as a programmer, you know):

sp_executesql Introduction and use of Execute I believe everyone uses the cooked, abbreviated to exec, except for the execution of stored procedures, is generally used to perform dynamic SQL
The new system stored procedures introduced in sp_executesql,sql2005 are also used to handle dynamic SQL, such as:
EXEC sp_executesql @sql, N ' @count int out, @id varchar ", @cou out
, @id
@sql to a dynamic SQL for consolidation
N ' @count int out, @id varchar (20) ' As a parameter list in the dynamic SQL to be spelled
@cou out, @id the list of external parameters that provide values for the parameter list in dynamic SQL

So what's the difference between them?

1, the biggest difference between them is the embedded parameters, such as the following statement
declare @sql nvarchar (2000)
DECLARE @id varchar (20)
Set @id = ' 1 '
Set @sql = ' SELECT count (*) from EMP where id= ' + @id
EXEC @sql
I want to get the count (*) out, with the traditional exec is not good, but with sp_executesql it is easy to do:
declare @sql nvarchar (2000)
DECLARE @cou int
DECLARE @id varchar (20)
Set @id = ' 1 '
Set @sql = ' Select @count =count (*) from EMP where [email protected] '
EXEC sp_executesql @sql, N ' @count int out, @id varchar ", @cou out, @id
Print @cou
2. Performance
As you can see, if you use Exec, because each incoming @id is not the same, each time the generated @sql is different, so that each time Sql2005 must be executed again to re-compile the dynamic SQL
But sp_executesql is not the same, due to the numerical parameterization, the dynamic SQL to be executed will never change, but the value of the parameters passed in the change, that every time the execution of the second with a recompile, the speed naturally much faster ha!

Attention:
1.sp_executesql requires dynamic SQL and dynamic SQL parameter lists to be nvarchar, such as the last example of @sql,n ' @count int out, @id varchar (20) ' I remember the varchar in sql2005, But after I hit the Sp3 patch, I can't, I have to nvarchar.
2. The parameter list of dynamic SQL must match the order of the parameter list of the externally supplied values, such as:
N ' @count int out, @id varchar ", @cou out, @id
@count corresponding @cou, @id corresponding @id
If they are not consistent, they must be explicitly marked, such as:
N ' @count int out, @id varchar ", @id = @id, @[email protected] out
3. The parameter list for dynamic SQL can have the same name as the parameter list parameter name of the externally supplied parameter

Assign exec execution results to variable output in SQL Server stored procedure