Shulou(Shulou.com)05/31 Report--

Most people don't understand the knowledge points of this article "How to execute dynamic SQL in SQL Server", so Xiaobian summarizes the following contents for everyone. The contents are detailed, the steps are clear, and they have certain reference value. I hope everyone can gain something after reading this article. Let's take a look at this article "How to execute dynamic SQL in SQL Server".

In SQL Server query statements using variables to represent table names, field names and other dynamic query methods called dynamic query.

When it is necessary to determine the SQL statement to be executed according to external input parameters, it is often necessary to dynamically construct SQL query statements. The most commonly used places are paging stored procedures and SQL statements for executing search queries.

A more general paging stored procedure, may need to pass table names, fields, filter conditions, sorting and other parameters, and for search, it may be necessary to dynamically execute SQL statements according to search conditions.

There are two ways to execute dynamic SQL statements in SQL Server: sp_executesql and exec.

sp_executesql has more advantages than exec, it provides input and output interfaces, input and output variables can be passed directly to SQL statements, exec can only be realized by concatenation, and security is not as high as executesql. Another advantage is sp_executesql, which enables reuse of execution plans, which greatly improves execution performance. Therefore, it is generally recommended to select sp_executesql to execute dynamic SQL statements.

sp_executesql stored procedure (recommended)

Using stored procedures provides an interface for input and output, and statements can be reused for execution.

When using sp_executesql, note that the SQL statement executed after it must be a Unicode encoded string, so when declaring a variable storing a dynamic SQL statement, it must be declared as nvarchar type, otherwise an error will be reported when executing "The procedure requires a parameter '@statement' of type 'ntext/nchar/nvarchar'." If you use sp_executesql to execute a SQL statement directly, you must add the capital letter N in front to indicate that the following string is encoded using Unicode type.

Grammar:

sp_executesql [ @stmt = ] statement [ { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,... n ]' } { , [ @param1 = ] 'value1' [ ,... n ] } ]1. Simple query declare @TableName varchar(50), @SqlString nvarchar(200), @CourseID int;set @TableName ='Coursetable ';set @CourseID = 1;set @SqlString = N'select * from ' + quotename (@TableName) + N'where ID = ' + cast (@CourseID as varchar(10));exec sp_executesql @SqlString;2. Use the input parameter declare @sql as nvarchar(100); set @sql = N'SELECT orderid,custid,empid,orderdate FROM Sales.Orders WHERE orderid=@orderid;';exec sys.sp_executesql @stmt = @sql, @params = N'@orderid AS INT', @orderid = 10248; 3. SQL statement create procedure sp_GetNameByUserId with output parameters (@userId varchar(100), @userName varchar(100) output)as declare @sql nvarchar(1000); set @sql=N'select @userName=UserName from Student where UserId=@userId'; exec sp_executesql @sql,N'@userId varchar(100),@userName varchar(100) output', @userId, @userName output;select @userName;

Synthesis example: two input parameters and one output parameter

declare @count int, @tableName nvarchar(50), @SQLString nvarchar(max), @proid int, @id int, @ParmDefinition nvarchar(max);set @tableName=N'mytable';set @proid=433;set @id=159;--set @sql=N'select @count=count(empid) from table27'set @SQLString=N'select @countOUT=count(empid) from ' + @tableName + N' where proid=@proid1 and id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.