下述方法测试通过 表test, 字段col1,col2,...执行下面的存储过程 testprc ,表的字段名已经修改了!create proc testprc as begin exec sp_rename 'test.col1','cc','COLUMN' end go
create table hdy(FID int ,FName varchar(10)) go create proc sp_hdy (@tabColName varchar(30), @newColName varchar(30)) as declare @Return int EXECUTE @Return=sp_rename @tabColName, @newColName, 'COLUMN' print @Return --返回值0(成功)或非零数字(失败) goexec sp_hdy 'hdy.FID', 'F1'
我简化后的存储过程如下: 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 gosp_alter_test 'test2.time11','time2' 调用后显示如下: 服务器: 消息 15253,级别 11,状态 1,过程 sp_rename,行 108 分析 SQL 标识符 'test2.time11 ' 时发现语法错误。 1 这是什么原因呢?
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
请问存储过程中调用存储过程是完全可行的,也是一个好的习惯,多调试一下吧。 给个例子给你看。 SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GOALTER 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 intset @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_1print @table_name+' @table_name at db_get_canpayt' print convert(nvarchar(20),@errcode)+ ' @errcode at db_get_canpayt' ENDGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
as
EXECUTE sp_rename @tabColName, @newColName, 'COLUMN'exec p4 'table1.col1', 'col2'
表test, 字段col1,col2,...执行下面的存储过程 testprc ,表的字段名已经修改了!create proc testprc
as
begin
exec sp_rename 'test.col1','cc','COLUMN'
end
go
create table hdy(FID int ,FName varchar(10))
go
create proc sp_hdy (@tabColName varchar(30), @newColName varchar(30))
as
declare @Return int
EXECUTE @Return=sp_rename @tabColName, @newColName, 'COLUMN'
print @Return --返回值0(成功)或非零数字(失败)
goexec sp_hdy 'hdy.FID', 'F1'
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
gosp_alter_test 'test2.time11','time2'
调用后显示如下:
服务器: 消息 15253,级别 11,状态 1,过程 sp_rename,行 108
分析 SQL 标识符 'test2.time11 ' 时发现语法错误。
1
这是什么原因呢?
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
给个例子给你看。
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOALTER 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 intset @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_1print @table_name+' @table_name at db_get_canpayt'
print convert(nvarchar(20),@errcode)+ ' @errcode at db_get_canpayt'
ENDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO