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 href="mailto:orderid=@orderid;'" 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)
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;
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);
