error handling in stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

error handling in stored procedure?

Author

Topic

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-06-15 : 02:14:37

Hi friends,

I have one query,

query is:
first i am fetching records from tbl_records where last 7 days and companyid=@companyid.

after that i am doing calculations.

if we don't have records from last 7 days means for the company means i don't want to do calculations i want to pass error message how to do.(error handling in sp)

if any body knows please give me idea.

Regards
Rama.

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-15 : 02:21:02

Hi Rama,

You can return 0 or 1 from stored procedure. 0 If you dont get results and 1 if you get some results in an output parameter or return value and then use this value in your application

Regards,
Asif Hameed

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-06-15 : 02:29:45

Hi sparrow37

Thankyou verymuch.

Regards
Rama

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-15 : 13:39:33

quote:
Originally posted by samsun125

Hi friends,

I have one query,

query is:
first i am fetching records from tbl_records where last 7 days and companyid=@companyid.

after that i am doing calculations.

if we don't have records from last 7 days means for the company means i don't want to do calculations i want to pass error message how to do.(error handling in sp)

if any body knows please give me idea.

Regards
Rama.

you can perform checks like if exists() to see if records are returned and then do calculations...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-15 : 14:09:16

Look in Books Online for: RAISERROR

What version of SQL are you using? If 2005 or later you can use TRY-CATCH blocks to trap errors. If 2000 or before you can use GOTOs to jump out. But, here is one sample for 2005:

BEGIN TRY

	--Select records for last 7 days
	SELECT .....

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR(N'No rows to calculate', 16, 1) 
	END
	
	-- Peform Calculations
END TRY
BEGIN CATCH
	-- Do stuff
END CATCH

Additionally, you can change the check for @@ROWCOUNT to use EXISTS or something as Visakh suggested.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-15 : 14:10:51

also see this to understand error handling inside procedures

http://www.sommarskog.se/error-handling-II.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources