SP与交易,但它得到错误 [英] SP with transaction but it getting error
本文介绍了SP与交易,但它得到错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在这种情况下,不能使用带有返回值的RETURN语句.
"CREATE/ALTER PROCEDURE"必须是查询批处理中的第一条语句.
我在sql中运行以下代码时遇到上述错误
A RETURN statement with a return value cannot be used in this context.
''CREATE/ALTER PROCEDURE'' must be the first statement in a query batch.
i am getting above error when i run the below code in sql
alter proc Usp_Archied_new
@fromdate date,
@todate Date,
@name varchar(200)
as
BEGIN TRAN
if not exists(select * from sys.objects where type_desc='USER_TABLE' and name='Process_Trn_backup' and name ='Core_Process_Dtls_backup' and name ='NonCore_Process_Dtls_Backup')
begin
alter table Process_Trn_backup add createdby varchar(150),Createdon date
---- SOME OPERATIONS ---
IF (@@ERROR <> 0) GOTO ERR_HANDLER
---- SOME OPERATIONS ---
IF (@@ERROR <> 0) GOTO ERR_HANDLER
---- SOME OPERATIONS ---
IF (@@ERROR <> 0) GOTO ERR_HANDLER
---- SOME OPERATIONS ---
IF (@@ERROR <> 0) GOTO ERR_HANDLER
---- SOME OPERATIONS ---
IF (@@ERROR <> 0) GOTO ERR_HANDLER
end
else
begin
alter table NonCore_Process_Dtls_Backup add createdby varchar(150),Createdon date
---- SOME OPERATIONS ---
IF (@@ERROR <> 0) GOTO ERR_HANDLER
---- SOME OPERATIONS ---
end
COMMIT TRAN
RETURN 0
ERR_HANDLER:
ROLLBACK TRAN
RETURN 1
推荐答案
http://www.dbforums.com/db2/698613-alter-table-stored -procedure.html [ ^ ]
我可以在sproc中看到以下alter语句.
I can see following alter statements in the sproc.
alter table Process_Trn_backup add createdby varchar(150),Createdon date
alter table NonCore_Process_Dtls_Backup add createdby varchar(150),Createdon date
您不能直接在存储过程中执行CREATE/ALTER语句.
You can not execute CREATE/ALTER statements directly in a stored procedure.
这篇关于SP与交易,但它得到错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文