create proc sp_mysp
as
select a.*, b.ClassId as 级别 into a from v_custmerManagerSumAsset a, run..CustmerManagerClass b where 1 <> 1 declare @i int, @sSqlString varchar(100) select @i = max(ClassId) from run..CustmerManagerClass
while @i > 0
begin
select @sSqlString = ClassPrescribe from run..CustmerManagerClass where ClassId = @i
exec('insert into a select *, ClassId = @i from v_custmerManagerSumAsset where ' + @sSqlString )
set @i = @i - 1
end select * from ago在执行的时候出问题了,
服务器: 消息 137,级别 15,状态 2,行 1
Must declare the variable '@i'.
服务器: 消息 137,级别 15,状态 2,行 1
Must declare the variable '@i'.
服务器: 消息 137,级别 15,状态 2,行 1
Must declare the variable '@i'.
服务器: 消息 137,级别 15,状态 2,行 1
Must declare the variable '@i'.
服务器: 消息 137,级别 15,状态 2,行 1
Must declare the variable '@i'.
服务器: 消息 137,级别 15,状态 2,行 1
Must declare the variable '@i'.
服务器: 消息 137,级别 15,状态 2,行 1
Must declare the variable '@i'.
解决方案 »
- 各位高手,帮我看这条语句什么地方错
- 存储过程中用参数存为文件名的问题
- 寻求SQL2000语句:要求把所有含有记录的表名都列出来
- 各位大哥,如何在一个存储过程里面调用另一个存储过程,并且利用它的返回值呢,谢谢,急!
- 一个触发器来写的难题
- 请各位看看这个存储过程是那里出错的???并请说明。先谢谢了。。。。
- 求助:求一段SQL
- 一个捆扰我的很久的一个问题,望各位赐教!!怎样用SELECT去查?三个表…………
- Rank() 求算法或优化方案
- MS SQL 中通过DB LINK 查询ORACLE那边的数据时,遇到要用到变量怎么写才正确?
- 求助:,初入SQL SERVER,一个非常菜的问题!
- foreign key的问题
/******************************************************************************************************************************************************
动态语句语法:exec\sp_executesql语法整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/
动态语句语法:--方法1查询表改为动态
select * from sysobjects
exec('select ID,Name from sysobjects')
exec sp_executesql N'select ID,Name from sysobjects'--多了一个N为unicode--方法2:字段名,表名,数据库名之类作为变量时,用动态SQL
declare @FName varchar(20)
set @FName='ID'
exec('select '+@FName+' from sysobjects where '+@FName+'=5' )
declare @s varchar(1000)
set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5'
exec sp_executesql @s--会报错
declare @s nvarchar(1000)--改为nvarchar
set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5'
exec sp_executesql @s--成功
--方法3:输入参数declare @i int,@s nvarchar(1000)
set @i=5
exec('select ID,Name from sysobjects where ID='+@i)set @s='select ID,Name from sysobjects where ID=@i'
exec sp_executesql @s,N'@i int',@i--此处输入参数要加上N--方法4:输出参数declare @i int,@s nvarchar(1000)
set @s='select @i=count(1) from sysobjects'--用exec
exec('declare @i int '+@s+' select @i')--把整个语句用字符串加起来执行--用sp_executesql
exec sp_executesql @s,N'@i int output',@i output--此处输出参数要加上N
select @i
--方法5:输入输出--用sp_executesql
declare @i int,@con int,@s nvarchar(1000)
set @i=5
select @s='select @con=count(1) from sysobjects where ID>@i'
exec sp_executesql @s,N'@con int output,@i int',@con output ,@i
select @con--用exec
declare @i int,@s nvarchar(1000)
set @i=5
select @s='declare @con int select @con=count(1) from sysobjects where ID>'+rtrim(@i)+' select @con'
exec(@s)
参照
改成
exec('insert into a select *, ClassId = '+str(@i)+' from v_custmerManagerSumAsset where ' + @sSqlString )在exec里面执行相当于另外一个环境了
as
select a.*, b.ClassId as 级别 into a from v_custmerManagerSumAsset a, run..CustmerManagerClass b where 1 <> 1 declare @i int, @sSqlString varchar(100) ,@s nvarchar(2000) select @i = max(ClassId) from run..CustmerManagerClass
while @i > 0
begin
select @sSqlString = ClassPrescribe --ClassPrescribe列的值是否为表达式
from run..CustmerManagerClass where ClassId = @i
set @s='insert into a select *, ClassId = '+rtrim(@i)+' from v_custmerManagerSumAsset where ' + @sSqlString
exec(@s)
set @i = @i - 1
end select * from a
exec时不能用函数str直接调用
exec('insert into a select *, ClassId = '+@i+' from v_custmerManagerSumAsset where ' + @sSqlString )
alter proc sp_mysp
as select a.*, b.ClassId as 级别 into #a from v_custmerManagerSumAsset a, run..CustmerManagerClass b where 1 <> 1 declare @i int, @sql varchar(8000), @sSqlString varchar(100) select @i = max(ClassId) from run..CustmerManagerClass
while @i > 0
begin
select @sSqlString = ClassPrescribe from run..CustmerManagerClass where ClassId = @i
--print @sSqlString
--print @i
set @sql = 'insert into #a select *, ClassId = ' + convert(char, @i) + ' from v_custmerManagerSumAsset where ' + @sSqlString
--print @sql
exec(@sql)
set @i = @i - 1
endgo搞定了.