try:
CREATE procedure prcTransactionsOfAccountAutoGen @iIncrement char(28) output
as
declare @cTransactionID varchar(28)
select @cTransactionID=max(cTransactionID) from TransactionsOfAccount
select @cTransactionID=isnull(cast(max(@cTransactionID) as varchar),0)+1
select @iIncrement=right('000...000'+cast(@cTransactionID as varchar),28)
CREATE procedure prcTransactionsOfAccountAutoGen @iIncrement char(28) output
as
declare @cTransactionID varchar(28)
select @cTransactionID=max(cTransactionID) from TransactionsOfAccount
select @cTransactionID=isnull(cast(max(@cTransactionID) as varchar),0)+1
select @iIncrement=right('000...000'+cast(@cTransactionID as varchar),28)
解决方案 »
- 跨服务器远程执行存储过程报 无法启动分布式事务,怎么办
- 高分求救,我受不了了.请高手救命!
- 求高手解答!
- 提示:OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息!
- SqlServer2008排序规则,哪个是UTF-8
- 再安装sql server的时候哪里可以设置表里面数据去分大小写啊!谢谢
- 怎么 比较 RS1和RS2里的 记录集有相同的 ..
- SQL Server 2000与SQL Server 7.0有什么区别???
- 汉康ERP_sql server 2000死锁怎么解决??
- 我重新装了MS SQL SERVER 7 后,我新建的数据库怎么变成灰色的啦!
- 紧急求助,连接多网卡系统上sql server经常连接不上啊
- 【CSDN】【郁闷中,写存储过程写到一半,语法检查说必须声明变量,怎么回事】
as
declare @cTransactionID char(28)
select @cTransactionID=isnull(max(cTransactionID),'0') from TransactionsOfAccount
select @iIncrement=cast(substring(@cTransactionID,1,28) as decimal(28,0))+1
as
declare @cTransactionID char(28)
select @cTransactionID=isnull(max(cTransactionID),'0') from TransactionsOfAccount
select @iIncrement=right('00000000000000000000000000'+cast((cast(substring(@cTransactionID,1,28) as decimal(28,0))+1)as varchar(28)),28)
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)
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种
@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
declare @iIncrement char(28)
select @cTransactionID=isnull(cast(max(cTransactionID) as varchar(28)),0)+1 from TransactionsOfAccount
select @iIncrement=right('000000000000000000000000000'+cast(@cTransactionID as varchar(28)),28)
print(@iIncrement)