DECLARE @c_ids VARCHAR(200)

SET @c_ids ='1,2'

--直接这样是无法执行的

--SELECT * FROM dbo.Student WHERE Class IN (@c_ids)

--1， Exec 执行SQL动态查询

Exec  ('SELECT * FROM dbo.Student WHERE Class IN ('+@c_ids+')')

-- 2，通过动态查询，过滤需要的列

DECLARE @rols VARCHAR(200)

SET @rols='StuName,StuAge,Class'

Exec  ('SELECT '+@rols+' FROM dbo.Student WHERE Class IN ('+@c_ids+')')

--3，执行SQL动态查询的另外一种方法，通过内置存储过程：sys.sp_executesql 执行SQL语句

DECLARE @sql NVARCHAR(200)

SET @sql='SELECT '+@rols+' FROM dbo.Student WHERE Class IN ('+@c_ids+')'

EXEC sys.sp_executesql @sql

--4， 存储过程动态执行SQL，以参数变量@agePara的形式给SQL语句传递变量

DECLARE @age INT

SET @age = 18

SET @sql='SELECT * FROM dbo.Student WHERE StuAge = @agePara '

EXEC sys.sp_executesql @sql,N'@agePara  int',@age 

--5， '' 两个' 转移成一个',选择出名字中包含有‘英’字的学生信息

DECLARE @txt NVARCHAR(10)

SET @txt = '英'

SET @sql='SELECT *  FROM dbo.Student WHERE StuName like ''%@namePara'' '

EXEC sys.sp_executesql @sql,N'@namePara   NVARCHAR(10)',@txt 

--6， 把需要输出的变量同样可以定义到变量中进行输出

DECLARE @rowCountResult INT

SET @sql='SELECT @rowCountResult=count(*) FROM dbo.Student WHERE StuAge = @agePara '

EXEC sys.sp_executesql @sql,N'@agePara  int,@rowCountResult int OutPut',@age ,@rowCountResult OUTPUT

SELECT @rowCountResult

--7，技巧：用Print 查看拼接的动态SQL是否正确

SELECT 'SELECT '+@rols+' FROM dbo.Student WHERE StuAge = @agePara '