SQL Server中执行动态SQL(2)-木庄网络博客

语法：

sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]

1、简单的查询

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;

2、使用输入参数

declare @sql as nvarchar(100);
set @sql = N'SELECT orderid,custid,empid,orderdate FROM Sales.Orders WHERE <a  rel="external nofollow"   target="_blank">orderid=@orderid;</a>';
exec sys.sp_executesql @stmt = @sql, @params = N'@orderid AS INT', @orderid = 10248;

3、带输出参数的SQL语句

create procedure sp_GetNameByUserId(@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;

综合实例：两个输入参数和一个输出参数

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<@id1';
set @ParmDefinition=N'@proid1 int,@id1 int,@countOUT   int   output';

exec sp_executesql
  @SQLString,
  @ParmDefinition,
  @proid1=@proid,
  @id1=@id,
  @countOUT=@count output;

select @count;

二、EXEC命令

支持普通字符和Unicode字符。exec没有输入输出参数，只能通过拼接的方式来实现。注意转义字符‘的使用。

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);

到此这篇关于SQL Server中执行动态SQL的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容