Error in creating procedure with roll back transaction

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

I am trying to create a roll back transaction for creating a SP, below the sql query i used.

When i execute am getting bellow error

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'proc'.
Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'proc'.

Please Correct me. It would be great if any one provide me the answer very soon.

Thanks in Advance...

begin tran cproc

begin try

create proc samplep(
@fileid int)
as
begin
end

end try
begin catch
IF @@ERROR <> 0

BEGIN

PRINT 'Errors Found ... Rolling back'

ROLLBACK TRANSACTION cproc

RETURN

END

ELSE

begin

PRINT 'No Errors ... Committing changes'

COMMIT TRANSACTION proc

end

end catch

Posted 2-Jul-13 4:38am

sathish89visit

Add a Solution

Comments

CHill60 2-Jul-13 19:48pm

If you replace "create proc samplep" with "create PROCEDURE samplep" does the first error go away perhaps? As for the 2nd error, the transaction is called "cproc" not "proc"

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)

HarisJayadev · Answer 1 · 2013-07-02T18:18:00

Solution 1

I think you can't use Try Catch block for creating stored procedure

Posted 2-Jul-13 18:18pm

HarisJayadev

ArunRajendra · Answer 2 · 2013-07-02T18:34:00

There are too many errors in you query. I have updated the syntax.

SQL

create proc samplep(
@fileid int)
as
begin

 begin tran cproc

begin try
print 'some logic'
end try

begin catch
IF @@ERROR <> 0

BEGIN

    PRINT 'Errors Found ... Rolling back'

    ROLLBACK TRANSACTION cproc

    RETURN

END

ELSE

begin

    PRINT 'No Errors ... Committing changes'

    COMMIT TRANSACTION cproc

end
end catch

end