is this the proper way to return an error message? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

is this the proper way to return an error message?

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-24 : 14:37:08

See the beginning and end. Thanks!

ALTER PROCEDURE [dbo].[proc_rhkprod_lock_items_for_paypal]
@cf_user_id nvarchar(100) = NULL
,@app_user_id int = NULL
,@transaction_guid uniqueidentifier = NULL
AS
BEGIN
SET NOCOUNT ON;

declare @tablepk int
,@tblpeoplefk int
,@cfuserid nvarchar(100)
,@tblproductsfk int
,@quantity int

if ( (@cf_user_id is null) and (@app_user_id is null) )
begin
goto error_handler
end

SET @transaction_guid = coalesce(@transaction_guid,newid())

declare c1 cursor forward_only for
select tablePK
,tblpeoplefk
,cfuserid
,tblProductsFK
,quantity
from rhkprod_tblCart
where 1 = 1
and tblpeoplefk = coalesce(@app_user_id,tblpeoplefk)
and cfuserid = coalesce(@cf_user_id,cfuserid)

open c1

fetch next
from c1
into @tablepk
,@tblpeoplefk
,@cfuserid
,@tblproductsfk
,@quantity

while @@fetch_status = 0

begin
insert into rhkprod_paypallock (
[cf_user_id]
,[tblproductsFK]
,[quantity]
,[timestamp]
,[transaction_guid]
)
values (
@cfuserid
,@tblproductsfk
,@quantity
,getdate()
,@transaction_guid
)

update rhkprod_tblproducts
set quantity = quantity - @quantity
where productid = @tblproductsfk

fetch next
from c1
into @tablepk
,@tblpeoplefk
,@cfuserid
,@tblproductsfk
,@quantity
end

close c1

deallocate c1

error_handler:
begin
select '@cf_user_id and @app_user_id not passed' as [error]
end

END

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 14:49:59

Not really.

That error will be returned in all instances.

Returning an error as a ResultSet is not ideal - although we do do it sometimes

Should be a RETURN value too - to indicate 0=No error, Non-zero=Error Code

Also worth returning the Error Number, and any other return values (e.g. Error Message, Identity allocated to a new record) as OUTPUT parameters.

Note that cursors are very inefficient and this task can be performed with normal set-based SQL which will be much more efficient

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources