动态sql语句用法实例：SQL Server2000下验证（部分转载）

USE Test
IF EXISTS (SELECT name FROM sysobjects
         WHERE name = 'test_tii' AND type = 'P')
   DROP PROCEDURE test_tii
GO

USE Test
GO
CREATE PROCEDURE test_tii
   @deviceIdInput char(10),
   @@tbName varchar(64) OUTPUT
AS
SELECT @@tbName = nextTbName FROM DeviceList WHERE deviceId = @deviceIdInput
GO

USE Test
GO
DECLARE @@myTbName varchar(64)
EXECUTE test_tii 'x123456789', @@myTbName OUTPUT
IF @@myTbName = null
BEGIN
   PRINT 'null'
END
ELSE
   PRINT @@myTbName

DECLARE @sqls nvarchar(1000) --   注意此处改为nvarchar(1000)  
SET @sqls = 'select * from ' + @@myTbName
--EXEC(@sqls)                               
EXEC sp_executesql @sqls     
GO

USE Test
IF EXISTS (SELECT name FROM sysobjects
         WHERE name = 'test_tinsert' AND type = 'P')
   DROP PROCEDURE test_tinsert
GO

USE Test
GO
CREATE PROCEDURE test_tinsert
   @userx varchar(16),
   @passwordx varchar(16),
   @roadx int,
   @descriptionx varchar(64) 
AS
DECLARE @@myTbName varchar(64)
EXECUTE test_tii 'x123456789', @@myTbName OUTPUT
DECLARE @sqls nvarchar(1000),@param nvarchar(100) --   注意此处改为nvarchar(1000)  
Set @sqls = 'Insert Into ' + @@myTbName + ' (username,password,road,description) Values('''
+ @userx + ''',''' + @passwordx + ''',@roadx,''' + @descriptionx + ''')' 
Set @param = '@roadx int'                        
EXEC sp_executesql @sqls, @param, @roadx 
GO

USE Test
GO
DECLARE @@myTbName varchar(64)
EXECUTE test_tii 'x123456789', @@myTbName OUTPUT
EXECUTE test_tinsert 'uisd','fdsf',4,'fdsfasfa'
DECLARE @sqls nvarchar(1000) --   注意此处改为nvarchar(1000)  
SET @sqls = 'select * from ' + @@myTbName       
EXEC sp_executesql @sqls     
GO

--USE Test
--GO
--select * from DeviceList
--GO

 
--以下部分为转载

动态sql语句基本语法

1 :普通SQL语句可以用Exec执行

 

eg: Select * from tableName

Exec( 'select * from tableName ')

Exec sp_executesql N 'select * from tableName ' -- 请注重字符串前一定要加N

 

2:字段名，表名，数据库名之类作为变量时，必须用动态SQL

 

eg:

declare @fname varchar(20)

set @fname = 'FiledName '

Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 itzhe.cn

Exec( 'select ' @fname ' from tableName ') -- 请注重 加号前后的 单引号的边上加空格

 

当然将字符串改成变量的形式也可

declare @fname varchar(20)

set @fname = 'FiledName ' --设置字段名

 

declare @s varchar(1000)

set @s = 'select ' @fname ' from tableName '

Exec(@s) -- 成功

exec sp_executesql @s -- 此句会报错

itzhe.cn

 

 

 

 

declare @s Nvarchar(1000) -- 注重此处改为nvarchar(1000)

set @s = 'select ' @fname ' from tableName '

Exec(@s) -- 成功

exec sp_executesql @s -- 此句正确

 

3. 输出参数

declare @num int,

@sqls nvarchar(4000)

set @sqls= 'select count(*) from tableName '

exec(@sqls) IT 者

 

--如何将exec执行结果放入变量中？

 

declare @num int, @sqls nvarchar(4000)

set @sqls= 'select @a=count(*) from tableName '

exec sp_executesql @sqls,N '@a int output ',@num output

select @num

 

本篇文章来源于 www.itzhe.cn 原文链接：http://www.itzhe.cn/html/sql/MsSQL/20071128/23304.html