SP with transaction but it getting error

Question

2.00/5 (1 vote)

See more:

SQL-Server-2008

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

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

Posted 26-Mar-12 20:45pm

Rajesh Duraisamy

Updated 28-Mar-12 5:25am

Liju Sankar

v5

Add a Solution

Comments

OriginalGriff 27-Mar-12 3:08am

If you want anyone to read that, then I suggest that you at least indent it to make our lives a little easier.
At the moment it is a large code dump and nothing else.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Liju Sankar · Answer 1 · 2012-03-28T05:24:00

Solution 2

http://www.dbforums.com/db2/698613-alter-table-stored-procedure.html[^]

Posted 28-Mar-12 5:24am

Liju Sankar

Saral S Stalin · Answer 2 · 2012-03-26T21:15:00

Solution 1

I can see following alter statements in the sproc.

SQL

alter table Process_Trn_backup add   createdby varchar(150),Createdon date

SQL

alter table NonCore_Process_Dtls_Backup add   createdby varchar(150),Createdon date

You can not execute CREATE/ALTER statements directly in a stored procedure.

Posted 26-Mar-12 21:15pm

Saral S Stalin

Comments

[no name] 28-Mar-12 11:22am

yes your point is valid