Issue with return status - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Issue with return status

Author

Topic

atlachar123456
Starting Member

33 Posts

Posted - 2011-10-20 : 12:33:35

begin try

select 1/0 as 'an error'

end try

begin catch

select ERROR_NUMBER() as 'ERROR_NUMBER',
ERROR_SEVERITY() as 'ERROR_SEVERITY',
ERROR_STATE() as 'ERROR_STATE',
LEFT(ERROR_PROCEDURE(),50) as 'ERROR_PROCEDURE',
ERROR_LINE() as 'ERROR_LINE' ,
LEFT(ERROR_MESSAGE(),40) as 'ERROR_MESSAGE'
end catch

I want to know what it returns...
when we execute a SP, it always have a return status...
as in 0 for success..it returns -ve values for error...
so thats what I am trying to find out what diff error codes are...

atlaaaaaaaa

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-20 : 12:54:26

I not sure I understand your question. You can use the RETURN statement to return a specific error code or return value. Are you trying to find a list of all the error codes?

One other thing, if you want to see what it returned from a stored procedure you can capture the return value in a variable, for example:


DECLARE @RetVal INT

EXEC @RetVal = <schema>.<Proc Name>
SELECT @RetVal

atlachar123456
Starting Member

33 Posts

Posted - 2011-10-20 : 15:55:58

Yes,I am trying to find out a list of all error codes in a SP?one more question my Sp is running fine with return status 0 in Dev and UAT...
but in prod it is returning -1...
so how do we know what's the error...

atlaaaaaaaa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 00:34:54

quote:
Originally posted by atlachar123456

Yes,I am trying to find out a list of all error codes in a SP?one more question my Sp is running fine with return status 0 in Dev and UAT...
but in prod it is returning -1...
so how do we know what's the error...

atlaaaaaaaa

check the condition for which you're returning -1 and see why its falling in that conditional code block

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources