请问存储过程中调用存储过程是完全可行的,也是一个好的习惯,多调试一下吧。
给个例子给你看。
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE db_get_canpayt @sql_str nvarchar(4000),
@table_name nvarchar(20) output,@errcode int output
AS --抓含canpaycount(可付汇数量)的po明细。
BEGIN
DECLARE @RC int
DECLARE @current_name nvarchar(20)
DECLARE @seq_len int
DECLARE @current_value nvarchar(100)
DECLARE @err int
declare @t1 nvarchar(4000) --temp string,using in command
declare @i int --i为计数器
declare @n int
--declare @temp_date datetime
--DECLARE @sqlselect nvarchar(4000)
--DECLARE @sqlfrom nvarchar(4000)
--DECLARE @sqlresult nvarchar(4000)
--declare @table_tname nvarchar(20)
declare @tbill_list_id nvarchar(20)
declare @tbill_list_sid int
declare @tcanpay int
set @errcode=0
set @current_name='canpaytt_seq'
set @seq_len=6
EXEC @RC = db_get_seq @current_name, @seq_len, @current_value OUTPUT , @err OUTPUT
if @err=0
begin
set @table_name='##canpaytt'+@current_value
--print @table_name + ' db_get_candec'
end
else
set @errcode=-1 --得到临时表名
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = @table_name AND type = 'U')
begin
set @t1='drop table '+@table_name
EXECUTE sp_executesql @t1
end
set @t1='select a.*,NULL as CanPayCount into '+@table_name
+' from ('+ @sql_str +') a'
--print @t1
EXECUTE sp_executesql @t1 --产生表
set @t1='declare cursor_1 cursor forward_only static read_only
for select TaxBillListID,TaxBillListRowID from '+@table_name
EXECUTE sp_executesql @t1
open cursor_1
if (@@CURSOR_ROWS<=0) set @errcode=-1
--print @@CURSOR_ROWS
if (@@CURSOR_ROWS>0)
begin
set @n=@@CURSOR_ROWS
set @i=0
while @i<@n
begin
fetch cursor_1 into @tbill_list_id,@tbill_list_sid
exec db_get_canpay @tbill_list_id, @tbill_list_sid, @tcanpay OUTPUT , @err OUTPUT
IF (@err=0)
BEGIN
set @t1='UPDATE '+@table_name+' SET CanPayCount='+convert(nvarchar(20),@tcanpay)
+'WHERE TaxBillListID='''+@tbill_list_id+''' AND TaxBillListRowID='+convert(nvarchar(20),@tbill_list_sid)
--print @t1
EXECUTE sp_executesql @t1
END
ELSE SET @errcode=-1
set @i=@i+1
end
end
close cursor_1
deallocate cursor_1
print @table_name+' @table_name at db_get_canpayt'
print convert(nvarchar(20),@errcode)+ ' @errcode at db_get_canpayt'
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create proc sp_alter_test(@oldColName varchar(220),@newColName varchar(220))
--要用varchar,你用了char固定220个字符,导致出错
as
begin
declare @return int
EXECUTE @return = sp_rename @oldColName,@newColName,'COLUMN'
print @return
end
go
我简化后的存储过程如下:
alter proc sp_alter_test(@oldColName char(220),@newColName char(220))
as
begin
declare @return int
EXECUTE @return = sp_rename @oldColName,@newColName,'COLUMN'
print @return
end
go