SP与交易，但它得到错误 [英] SP with transaction but it getting error

问题描述

在这种情况下，不能使用带有返回值的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屋！