这样用不对.
当表名称为变量时,必须用execute
改成:
execute('select '+ @vTempID + '=isnull(max(vID),'0') from '+ @Table +' where vID=''' +@vSourceID +'''')
意思是这样, 你调试一下.
当表名称为变量时,必须用execute
改成:
execute('select '+ @vTempID + '=isnull(max(vID),'0') from '+ @Table +' where vID=''' +@vSourceID +'''')
意思是这样, 你调试一下.
解决方案 »
- SQL server 怎么写存储过程
- 关于添加组件的问题,无法选择“对现有SQL Server实例进行升级、删除或添加组件”选项。
- “alter table Querrynote1 drop column @room”,这个语句有问题吗?
- 我该改行换工作吗?
- 各位MSSQL高手,帮我看一下!
- 初学者的烦恼
- 求字符串判断存在的问题!
- 多个客户端,同时读取一个表里的数据,select top 100 * from table,怎样防止客户端读出的数据重复?? sql server数据库
- 怪:Sqlserver中某数据表在一段时间内 update、select成功而 insert 失败???在线恭候
- 希望sql server高手来共同探讨的问题?(低手免看)
- 【CSDN】【一个求得自动编号的存储过程,请赐教】
- 【CSDN】【续】【多级分类档案ID自动编码存储过程的实现遇到难题,大家帮忙看看,跪谢】
set @sql='select @vTempID=isnull(max(vID),'0') from '+@Table+' where vID='+rtrim(@vSourceID)
exec sp_executesql @sql,N'@vTempID varchar(30) output',@vTempID output
declare @sql nvarchar(8000)
set @sql='select @vTempID=isnull(max(vID),'0') from '+@Table+' where vID='''+@vSourceID+''''
exec sp_executesql @sql,N'@vTempID varchar(30) output',@vTempID output
CREATE procedure prcIDAutoGen @vSourceID varchar(30),@cIsSubNode char(1),@Table varchar(20), @vIncrement varchar(30) output
as
declare @vTempID varchar(30)
if @cIsSubNode='y'
exec('select @vTempID=isnull(max(vID),''0'') from '+@Table+' where vID=@vSourceID')
else
begin
exec('select @vTempID=isnull(max(vID),''0'') from '+@Table+' where vID=@vSourceID')
select @vIncrement=right('0000'+cast((cast(substring(@vTempID,1,30) as decimal(30,0))+1)as varchar(30)),len(ltrim(rtrim(@vSourceID))))
end
set @sql='select @vTempID=isnull(max(vID),'0') from '+@Table+' where vID='''+@vSourceID+''''
exec sp_executesql @sql,N'@vTempID varchar(30) output',@vTempID output
TO:J9988nvarchar 最大4000
@vSourceID varchar(30),
@cIsSubNode char(1),
@Table varchar(20),
@vIncrement varchar(30) output
as
declare @vTempID varchar(30),@s nvarchar(4000)
if @cIsSubNode='y'
begin
set @s='select @vTempID=isnull(max(vID),''0'') from ['+@Table+'] where vID=@vSourceID'
exec sp_executesql @s
,N'@vTempID varchar(30) out,@vSourceID varchar(30)'
,@vTempID out,@vSourceID
end
else
begin
set @s='select @vTempID=isnull(max(vID),''0'') from ['+@Table+'] where vID=@vSourceID'
exec sp_executesql @s
,N'@vTempID varchar(30) out,@vSourceID varchar(30)'
,@vTempID out,@vSourceID
select @vIncrement=right('0000'+cast((cast(substring(@vTempID,1,30) as decimal(30,0))+1)as varchar(30)),len(ltrim(rtrim(@vSourceID))))
end
回复人: welyngj(平平淡淡) ( ) 信誉:100 2004-01-15 21:11:00 得分:0
have a try:
CREATE procedure prcIDAutoGen @vSourceID varchar(30),@cIsSubNode char(1),@Table varchar(20), @vIncrement varchar(30) output
as
declare @vTempID varchar(30)
if @cIsSubNode='y'
exec('select @vTempID=isnull(max(vID),''0'') from '+@Table+' where vID=@vSourceID')
else
begin
exec('select @vTempID=isnull(max(vID),''0'') from '+@Table+' where vID=@vSourceID')
select @vIncrement=right('0000'+cast((cast(substring(@vTempID,1,30) as decimal(30,0))+1)as varchar(30)),len(ltrim(rtrim(@vSourceID))))
end
帮忙确认一下这样做是不对的,我想是不对的,要么所有参数都用+@参数+方式连接起来,或是用sp_executesql2谢谢zjcxc(邹建)你的答案,我想请问你,为什么要加[]括号呀?
要说明的是
这种状况是由于SQL的解析方式决定的
当使用诸如动态SQL时
采用exec执行string是很常用的方法。
create table CustomerClass(
vID varchar(30) constraint pkCustomerClass primary key ,
vCustomerClassName varchar(40) NOT NULL,
vRes varchar(80),
vParentID varchar(30) NULL,
iDepth Int NULL
)存储过程为
CREATE procedure prcIDAutoGen
@vSourceID varchar(30),
@iDepth int,
@iIsSubNode int,
@Table varchar(20),
@vIncrement varchar(30) output
as
begin
/*本存储过程使用*/
declare @iLen int
declare @vTempID varchar(30)
/*sp_executesql使用*/
declare @SQLString nvarchar(500)
/*当添加类型为子结点时*/
if @iIsSubNode =1
begin
/*深度+1*/
set @iDepth=@iDepth+1
/*新建一个临时表*/
select @vSourceID as vID into #t
print 'step 1'
/*查询是否有原记录*/
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
print 'step 2'
/*如果存在原记录,则查询此记录的下一级所有子记录*/
if @@rowcount > 0
begin
set @SQLString=N'insert #t select vID from '+@Table +' where vParentID in (select vID from #t) and vID not in (select vID from #t) and iDepth=@iDepth'
exec sp_executesql @SQLString,N'@iDepth int',@iDepth
print 'step 3'
end
/*得到子记录的最大编号*/
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from #t'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output',@vTempID output
print 'step 4'
/*计算得到新的节点长度*/
set @iLen=@iDepth*3
/*返回新的最大编号*/
select @vIncrement=right(cast((cast(substring(@vTempID,1,@iLen) as decimal(3,0))+1)as varchar),@iLen)
print 'step 5'
end
else
begin
/*查询是否有原记录*/
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
/*如果存在原记录,则查询此记录的同一级所有记录*/
print 'test 1'
if @@rowcount > 0
begin
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from '+@Table+' where vID in (select vID from '+@Table+' where iDepth='+@iDepth+')'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output',@vTempID output
print 'test 2'
end
/*返回新的最大编号*/
set @iLen=len(ltrim(rtrim(@vSourceID)))
select @vIncrement=right('000'+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)
print 'test 3'
end
end现在的问题如下:
1、用以下代码测试
declare @c varchar(30)
exec prcIDAutoGen " ",0,1,"customerclass",@vIncrement=@c output
select @c
得到错误信息
(所影响的行数为 1 行)
step 1
(所影响的行数为 0 行)
step 2
step 4
服务器: 消息 8114,级别 16,状态 5,过程 prcIDAutoGen,行 40
将数据类型 varchar 转换为 numeric 时出错。2用以下代码测试
declare @c varchar(30)
exec prcIDAutoGen " ",0,0,"customerclass",@vIncrement=@c output
select @c
输出结果为NULL我联机帮助看了半天,却仍不知是为什么?向高手请教,再次感谢
@vSourceID varchar(30),
@iDepth int,
@iIsSubNode int,
@Table varchar(20),
@vIncrement varchar(30) output
as
begin
/*本存储过程使用*/
declare @iLen int
declare @vTempID varchar(30)
/*sp_executesql使用*/
declare @SQLString nvarchar(500)
/*当添加类型为子结点时*/
if @iIsSubNode =1
begin
/*深度+1*/
set @iDepth=@iDepth+1
/*新建一个临时表*/
select @vSourceID as vID into #t
print 'step 1'
/*查询是否有原记录*/
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
--** 错误1:print 语句会影响@@rowcount的值,所以要去掉
-- print 'step 2' --*********这个会影响@@rowcount的值,要去掉才能得到正确结果 /*如果存在原记录,则查询此记录的下一级所有子记录*/
if @@rowcount > 0
begin
set @SQLString=N'insert #t select vID from '+@Table +' where vParentID in (select vID from #t) and vID not in (select vID from #t) and iDepth=@iDepth'
exec sp_executesql @SQLString,N'@iDepth int',@iDepth
print 'step 3'
end
/*得到子记录的最大编号*/
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from #t'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output',@vTempID output
print 'step 4'
/*计算得到新的节点长度*/
set @iLen=@iDepth*3
/*返回新的最大编号*/--** 错误2:如果表中无数据,且@vSourceID为空,则@vTempID也为空,这时要单独处理,否则出错
if ltrim(@vTempID)=''
set @vIncrement=@vTempID
else
begin
--** 错误3:decimal()中不能用变量
-- select @vIncrement=right(cast((cast(substring(@vTempID,1,@iLen) as decimal(3,0))+1)as varchar),@iLen)
set @SQLString='select @vIncrement=right(cast((cast(substring(@vTempID,1,'+cast(@iLen as varchar)+') as decimal(3,0))+1)as varchar),'+cast(@iLen as varchar)+')'
exec sp_executesql @SQLString
,N'@vIncrement varchar(30) output,@vTempID varchar(30)'
,@vIncrement out,@vTempID
print 'step 5'
end
end
else
begin
/*查询是否有原记录*/
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
/*如果存在原记录,则查询此记录的同一级所有记录*/--** 错误4:print 语句会影响@@rowcount的值,所以要去掉
-- print 'test 1'
if @@rowcount > 0
begin
--** 错误5:未进行数据类型转换
-- set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from '+@Table+' where vID in (select vID from '+@Table+' where iDepth='+@iDepth+')'
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from '+@Table+' where vID in (select vID from '+@Table+' where iDepth=@iDepth)'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output,@iDepth int',@vTempID output,@iDepth
print 'test 2'
end
/*返回新的最大编号*/
set @iLen=len(ltrim(rtrim(@vSourceID)))--** 错误6:decimal()中不能用变量
-- select @vIncrement=right('000'+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)
set @SQLString='select @vIncrement=right(''000''+cast((cast(substring(@vTempID,1,'+cast(@iLen as varchar)+') as decimal(30,0))+1)as varchar),'+cast(@iLen as varchar)+')'
exec sp_executesql @SQLString
,N'@vIncrement varchar(30) output,@vTempID varchar(30)'
,@vIncrement out,@vTempID print 'test 3'
end
end
go
@vSourceID varchar(30),
@iDepth int,
@iIsSubNode int,
@Table varchar(20),
@vIncrement varchar(30) output
as
begin
declare @iLen int
declare @vTempID varchar(30)
declare @SQLString nvarchar(500)
if @iIsSubNode =1
begin
set @iDepth=@iDepth+1
set @iLen=@iDepth*3
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
if @@rowcount > 0
begin
select @vSourceID as vID into #t
set @SQLString=N'insert #t select vID from '+@Table +' where vParentID in (select vID from #t) and vID not in (select vID from #t) and iDepth=@iDepth'
exec sp_executesql @SQLString,N'@iDepth int',@iDepth
if @@rowcount > 0
begin
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from #t'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output',@vTempID output
set @SQLString='select @vIncrement=right(''000''+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)'
exec sp_executesql @SQLString,N'@vIncrement varchar(30) output,@vTempID varchar(30),@iLen int',@vIncrement out,@vTempID,@iLen
end
else
begin
select @vIncrement=ltrim(rtrim(@vSourceID))+'001'
end
end
else
begin
select @vIncrement='001'
end
end
else
begin
set @iLen=len(ltrim(rtrim(@vSourceID)))
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
if @@rowcount > 0
begin
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from '+@Table+' where vID in (select vID from '+@Table+' where iDepth=@iDepth)'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output,@iDepth int',@vTempID output,@iDepth
set @SQLString='select @vIncrement=right(''000''+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)'
exec sp_executesql @SQLString,N'@vIncrement varchar(30) output,@vTempID varchar(30),@iLen int',@vIncrement out,@vTempID,@iLen
end
else
begin
select @vIncrement='001'
end
end
end
1、set @SQLString='select @vIncrement=right(''000''+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)'
exec sp_executesql @SQLString,N'@vIncrement varchar(30) output,@vTempID varchar(30),@iLen int',@vIncrement out,@vTempID,@iLen2、'+cast(@iLen as varchar)+')'3、'@iLen+')'方法1、2正确,3错误,建议使用第1种