动态SQL初探

--1 、普通SQL语句   

Select CustomerID,

CompanyName,ContactName,Address

,City,Phone From dbo.Customers  

GO

EXEC ('Select CustomerID,

CompanyName,ContactName,Address

,City,Phone From dbo.Customers')

GO

EXEC Sp_ExecuteSql N'Select CustomerID,

 CompanyName,ContactName,Address,City,Phone From dbo.Customers'----- 注意字符串加N   

---2、字段名，表名为变量时，必须用动态SQL

Declare @fname varchar(100)

Set @fname='CustomerID,CompanyName,ContactName,Address,City,Phone'

Declare @StrSQL varchar(1000)

Set @StrSQL='Select '+@fname+' from dbo.Customers'

Exec(@StrSQL) -- 成功

--exec sp_executesql @StrSQL -- 此句会报错,因为定义@StrSQL数据类型为varchar,改为n varchar即可

GO

Declare @fname varchar(100)

Set @fname='CustomerID,CompanyName,ContactName,Address,City,Phone'

Declare @StrSQL Nvarchar(1000) --改为nvarchar(1000)

Set @StrSQL = 'select ' + @fname + ' from dbo.Customers'

Exec(@StrSQL)

exec sp_executesql @StrSQL

 

--3、以参数形式输出

Declare @StrSQL Nvarchar(1000)

Set @StrSQL='Select Count(*) From dbo.Customers'

Exec(@StrSQL)

Go

--将结果放到变量中,输出参数

Declare @Counter int

Declare @StrSQL Nvarchar(1000)

Set @StrSQL='Select @Counter=Count(*) From dbo.Customers'

Exec Sp_ExecuteSql @StrSQL,N'@Counter int output',@Counter output

Select Counter=@Counter