动态T-SQL语句常見問題與解決方案

--> Title  : 动态T-SQL语句常見問題與解決方案

--> Author : wufeng4552

--> Date   : 2009-11-24 10:11:10

if object_id('[tb]') is not null drop table [tb]

go

create table [tb] (dt nvarchar(20),ID int)

insert into [tb]

select '0718093429',1 union all

select '0728043205',2 union all

select '0728032243',3

--1．忘記類型轉換

DECLARE @value int

SET @value=2

DECLARE @sql varchar(8000)

--錯誤的寫法,類型不一致

SET @sql='SELECT * FROM tb WHERE ID='+@value

EXEC(@sql)

/*

訊息245，層級16，狀態1，行10

將varchar 值'SELECT * FROM tb WHERE ID=' 轉換成資料類型int 時，轉換失敗。

*/

--正確寫法,轉換類型

SET @sql='SELECT * FROM tb WHERE ID='+ltrim(@value)

EXEC(@sql)

--或者

EXEC('SELECT * FROM tb WHERE ID='+@value)

 

--2 ．字符串邊界符問題

DECLARE @value varchar(10)

SET @value='0728043205'

DECLARE @sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000)

--常見錯誤

SET @sql1='SELECT * FROM tb WHERE dt='+@value

SET @sql2='SELECT * FROM tb WHERE dt=''+@value+'

PRINT @sql1

PRINT @sql2

/*

SELECT * FROM tb WHERE dt=0728043205

SELECT * FROM tb WHERE dt='+@value+

*/

--正確寫法

SET @sql3='SELECT * FROM tb WHERE dt='''+@value+''''

PRINT @sql3

/*

SELECT * FROM tb WHERE dt='0728043205'

*/

EXEC(@SQL1)

EXEC(@SQL2)

/*

訊息105，層級15，狀態1，行1

遺漏字元字串'+@value+' 後面的引號。

訊息102，層級15，狀態1，行1

接近'+@value+' 之處的語法不正確。

*/

EXEC(@SQL3)

/*

dt                   ID

-------------------- -----------

0728043205           2

 

(1 個資料列受到影響)

*/

--3．表名及字段名的问題

--常見錯誤寫法

DECLARE @tbname sysname

SET @tbname='tb'

SELECT * FROM @tbname

GO

DECLARE @tbname sysname

SET @tbname='[tb]'

EXEC sp_executesql N'SELECT * FROM @tbname',N'@tbname sysname',@tbname

GO

--正確寫法

DECLARE @SQL NVARCHAR(100),@TBNAME SYSNAME

SELECT @TBNAME='TB'

SET @SQL='SELECT * FROM '+@TBNAME

EXEC(@SQL)

GO

--或

DECLARE @SQL NVARCHAR(100),@TBNAME SYSNAME

SELECT @TBNAME='TB'

SET @SQL='SELECT * FROM '+@TBNAME

EXEC SP_EXECUTESQL @SQL

--4．返回值的問題

--常見錯誤寫法

DECLARE @DT sysname,@sql varchar(100)

SET @sql='SELECT @DT=DT FROM TB where id=1'

EXEC(@sql)

SELECT @DT

GO

DECLARE @DT sysname,@sql varchar(100)

SET @sql='SELECT '+@DT+'=DT FROM TB where id=1'

EXEC(@sql)

SELECT @DT

GO

--正確寫法

DECLARE @SQL NVARCHAR(200)

SET @SQL='DECLARE @DT SYSNAME SELECT @DT=DT FROM TB WHERE ID=1 SELECT @DT'

EXEC(@SQL)

--或者

DECLARE @SQL NVARCHAR(200),@DT SYSNAME

SET @SQL=N'SELECT @DT=DT FROM TB WHERE ID=1'

EXEC SP_EXECUTESQL @SQL,N'@DT SYSNAME OUTPUT',@DT OUTPUT

SELECT @DT