In SQL Server, the execution result of Exec is assigned to the variable output in the stored procedure. sqlserverexec

In SQL Server, the execution result of Exec is assigned to the variable output in the stored procedure. sqlserverexec

Background:

In this case, the table name and some attributes of the table (the primary key ID is used here) are dynamically passed to the stored procedure, and then some field values of a data item are identified using these two variables, then, use these values for logical operations (your own logic) and output the results. If you don't want to talk about it, check whether it is the result you want:

 

Note: [region 1 is the table to be tested] [Region 2 is the table data] [region 3 is the jan + feb + mar column value and 5 of the table data]

The Stored Procedure Code is as follows:

It can be seen that since the values of jan, feb, and mar columns can be obtained, it is easy to make your own judgment later. I will not go into details.

Because the study is not refined, the above conclusion is inspired by others, the relevant link: http://www.cnblogs.com/wanyuan8/archive/2011/11/09/2243483.html

Below is a post posted by the original author (as a programmer, you know ):

Sp_executesql introduction and use of execute I believe everyone is familiar with it, abbreviated as exec. In addition to executing stored procedures, it is generally used to execute dynamic SQL
New system stored procedures introduced in sp_executesql and sql2005 are also used to process dynamic SQL statements, such:
Exec sp_executesql @ SQL, n' @ count int out, @ id varchar (20) ', @ cou out
, @ Id
@ SQL: a dynamic SQL statement.
N' @ count int out, @ id varchar (20) 'indicates the list of parameters in the assembled dynamic SQL statement.
@ Cou out, @ id is the external parameter list that provides values for the parameter list in dynamic SQL.

So what are the differences between them?

1. The biggest difference between them is embedded parameters, as shown in 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 pass the obtained count (*). It is difficult to use traditional exec, but it is easy to use sp_executesql:
Declare @ SQL nvarchar (2000)
Declare @ cou int
Declare @ id varchar (20)
Set @ id = '1'
Set @ SQL = 'select @ count = count (*) from emp where id = @ id'
Exec sp_executesql @ SQL, n' @ count int out, @ id varchar (20) ', @ cou out
, @ Id
Print @ cou
2. Performance
As you can see, if exec is used, because the @ id passed in each time is different, the @ SQL generated each time is different. In this way, each time Sql2005 is executed, the dynamic SQL to be executed must be re-compiled once.
However, sp_executesql is different. Because the value is parameterized, the dynamic SQL to be executed will never change, but the value of the input parameter will change. Then, re-compile the SQL statement in seconds during each execution, the speed is naturally much faster!

Note:
1. sp_executesql requires that the dynamic SQL and dynamic SQL parameter lists must be Nvarchar. For example, in the previous example, @ SQL, n' @ count int out, @ id varchar (20) 'I remember that Varchar can be used in sql2005, but it won't work after I patch Sp3. It must be Nvarchar.
2. The parameter list of dynamic SQL must be consistent with that of the parameter list of external values, for example:
N' @ count int out, @ id varchar (20) ', @ cou out, @ id
@ Count corresponds to @ cou, @ id corresponds to @ id
If they are inconsistent, they must be explicitly marked, for example:
N' @ count int out, @ id varchar (20) ', @ id = @ id, @ count = @ cou out
3. The parameter list of dynamic SQL can have the same name as the parameter list of external parameters.

How does mssql exec assign the result to a variable?

The 2nd parameters of SP_EXECUTESQL are used to define the data of specific parameters when the OUTPUT parameter is executed. Add OUTPUT1> BEGIN2> DECLARE @ SQL NVARCHAR (200); 3> DECLARE @ name VARCHAR (10 ); 4> SET @ SQL = 'select @ name = name FROM test_dysql WHERE id = 1'; 5> PRINT @ SQL; 6> EXEC SP_EXECUTESQL @ SQL, n' @ name VARCHAR (10) output', @ name OUTPUT; 7> PRINT @ name; 8> END9> goSELECT @ name = name FROM test_dysql WHERE id = 1A

SQL: Can the output result of a stored procedure be passed to a variable?

Execute the stored procedure and define an output variable in it. The most returned value is received by a variable in an external program.