Dynamic SQL execution, note: Exec sp_executesql can actually implement parameter query and output parameters

This article transferred from: http://www.cnblogs.com/hnsdwhl/archive/2011/07/23/2114730.html

When it is necessary to determine the SQL statement to execute based on the parameters of the external input, it is often necessary to construct SQL query statements dynamically, which is more commonly used by the paging stored procedure and the SQL statement that executes the search query. A more general paging stored procedure, may need to pass in table name, field, filter conditions, sorting parameters, and for the search, may be based on the search criteria to determine the dynamic execution of SQL statements.

There are two ways to execute dynamic SQL statements in SQL Server, namely exec and sp_executesql. sp_executesql is relatively more advantageous, it provides an input-output interface that can pass input and output variables directly to an SQL statement, and exec can only be implemented by stitching. Another advantage is that sp_executesql can reuse the execution plan, which greatly improves the performance of the execution. Therefore, it is generally recommended to choose sp_executesql to execute dynamic SQL statements.

One thing to note about using sp_executesql is that the SQL statement that is executed after it must be a Unicode-encoded string, so you must declare the variable that stores the dynamic SQL statement as the nvarchar type, or the "procedure requires type as" when executing. Ntext/nchar/nvarchar ' parameter ' @statement ' "error, if you are using sp_executesql to execute SQL statements directly, you must precede the uppercase N to indicate that subsequent strings are encoded using Unicode type.

Here's a look at several scenarios for dynamically executing SQL statements

1. Common SQL statements

(1) EXEC (' select * from Student ')

(2) EXEC sp_executesql n ' select * from Student '--here must add N, otherwise it will error

2. SQL statements with parameters

(1) Declare @sql nvarchar (1000)
DECLARE @userId varchar (100)
Set @userId = ' 0001 '
Set @sql = ' SELECT * from Student where userid= ' [email protected]+ ' '

EXEC (@sql)

(2) Declare @sql nvarchar (1000)
DECLARE @userId varchar (100)
Set @userId = ' 0001 '
Set @sql =n ' select * from Student where [email protected] '
EXEC sp_executesql @sql, N ' @userId varchar (+) ', @userId

From this example, it can be seen that using sp_executesql can directly write parameters in the SQL statement, and exec needs to use stitching, which to some extent can prevent SQL injection, so sp_executesql has higher security. It is also important to note that variables that store SQL statements must be declared as nvarchar types.

(3) SQL statement with output parameters

CREATE PROCEDURE Sp_getnamebyuserid

(

@userId varchar (100),

@userName varchar () output

)

As

declare @sql nvarchar (1000)

Set @sql =n ' select @userName =username from Student where [email protected] '

exec sp_executesql N ' @userId varchar, @userName varchar output ', @userId, @userName output

Select @userName

Dynamic SQL execution, note: Exec sp_executesql can actually implement parameter query and output parameters