[dba-VB] Parameter objects in C# and ADO.

Hi Gustav at all,

BTW, have you ever seen the following below error handling in CRUD SPs as I
have found in one of my customers' databases?
Isn't an overkill?
Or do you use even more detailed/elaborated error handling in your CRUD SPs?
How do you organize error handling in your CRUD SPs? (I usually just return
error code in RETURN statement).

CREATE procedure [dbo].[GettblUserByUsername] 
@Username varchar(20),
@ErrorDesc varchar(100) output,
@ErrorNo int output,
@RowsAffected int output
AS
DECLARE @lErrorNo INTEGER	
DECLARE @lRowsAffected INTEGER	
Begin
	
	SELECT tblUser.tblUserId,
		tblUser.tblUserGroupId, 
		tblUserGroup.groupCode as UserGroup,
		tblUser.Username, 
		tblUser.tblUserLevelId
	FROM tblUser
	left outer join tblUserGroup
		on tblUser.tblUserGroupId = tblUserGroup.tblUserGroupId
	WHERE tblUser.Username = @username
	and tblUser.Authorised = 1

	select @lErrorNo = @@ERROR, @lRowsAffected = @@ROWCOUNT

	IF @lErrorNo > 0
	Begin		
		set @ErrorDesc = 'error with get of tblUser'
		set @ErrorNo = @lErrorNo
		set @RowsAffected = 0
		RETURN 
	End
	Else
	Begin
		set @ErrorDesc = 'Get was Successful for tblUser'
		set @ErrorNo = 0
		set @RowsAffected = @lRowsAffected
		RETURN
	End
end

Thank you.

--
Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, November 27, 2009 1:45 PM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Parameter objects in C# and ADO.

Hi Mark et al

What's the big deal? Are parameters costly? Hardly in money, but in
resources?

/gustav


<<< snip >>>
 

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4641 (20091127) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru