22.动态SQL语句处理常见问题案例-蒲公英云

22.动态SQL语句处理常见问题案例

--1．数据类型转换的问题
DECLARE @value int
SET @value=100
DECLARE @sql varchar(8000)
SET @sql='SELECT * FROM tbname WHERE col='+@value
EXEC(@sql)
GO
/*======================================================*/
--2．字符串边界符的问题
DECLARE @value varchar(10)
SET @value='aa'
DECLARE @sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000)
SET @sql1='SELECT * FROM tbname WHERE col1='+@value
SET @sql2='SELECT * FROM tbname WHERE col1=''+@value+'
SET @sql3='SELECT * FROM tbname WHERE col1='''+@value+''''
GO
DECLARE @value varchar(10)
SELECT @value='a''a'
DECLARE @sql varchar(8000)
SET @sql='SELECT * FROM tbname WHERE col='''+@value+''''
EXEC(@sql)
GO
/*======================================================*/
--3．表名及字段名的问题
DECLARE @tbname sysname
SET @tbname='sysobjects'
SELECT * FROM @tbname
GO
DECLARE @tbname sysname
SET @tbname='sysobjects'
EXEC sp_executesql N'SELECT * FROM @tbname',N'@tbname sysname',@tbname
GO
/*======================================================*/
--4．返回值的问题
DECLARE @tbname sysname,@sql varchar(100)
SET @sql='SELECT @tbname=name FROM sysobjects where id=1'
EXEC(@sql)
SELECT @tbname
GO
DECLARE @tbname sysname,@sql varchar(100)
SET @sql='SELECT '+@tbname+'=name FROM sysobjects where id=1'
EXEC(@sql)
SELECT @tbname