SQL Server(00):执行动态SQL
SQL Server(00):执行动态SQL
在Sql Server查询语句中使用变量表示表名、列字段名等动态查询方式。
方法一:EXEC命令(支持普通字符和Unicode字符)
declare @sql as nvarchar(100); set @sql = N'PRINT ''这条消息是动态SQL命令打印的.'';'; exec ( @sql ); declare @sql as nvarchar(100); declare @OrderIDs as nvarchar(50) = N'10248,10249,10250'; set @sql = N'SELECT * FROM Sales.Orders WHERE orderid IN (' + @OrderIDs + N');'; exec ( @sql );
方法二:sp_executesql存储过程(提供了输入输出的接口,语句可以重用执行。只支持Unicode字符)
简单的查询
declare @TableName varchar(50), @SqlString nvarchar(200), @CourseID int; set @TableName = '课程表'; set @CourseID = 1; set @SqlString = N'select * from ' + quotename(@TableName) + N'where ID = ' + cast(@CourseID as varchar(10)); exec sp_executesql @SqlString;
使用接口(支持参数的输入输出)
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;