Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Fri Nov 27 08:59:16 CST 2009
Hi John -- This kind of "excessive" execution result/error information is useful in my opinion for non-CRUD SPs. But for CRUD ones one can just use SPs return value: - if it's zero or positive - all is OK and returned value is @@ROWCOUNT; - if it's negative - then this is @@ERROR (if actual @@ERROR value will be positive just return it as negative number). If some kinds of runtime errors happen in SPs then they can be trapped by calling C# code try/catch block and actual error message can be obtained from System.Data.SqlClient.SqlException. Well, above is what I'd call a "lightweight" error handling approach - and it works well in many real life systems AFAIK... But for long running (set of) SPs one can use "heavyweight" error handling approach using T-SQLs TRY...CATCH features as described in MS SQL books online (local link): ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/248df62a-7334-4bca-8262- 235a28f4b07f.htm I could be missing something. Please correct me where I'm wrong, please write about your approach to T-SQL (and calling C# code) error handling. Thank you. -- Shamil -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Friday, November 27, 2009 5:11 PM To: Discussion concerning Visual Basic and related programming issues. Subject: Re: [dba-VB] Parameter objects in C# and ADO. RowsAffected is not part of the error stuff and I definitely want that info in most cases. I haven't done this yet but I don't think that getting back the error code and description is excessive. If it is possible to easily and unambiguously look up the error code and get the exact description, each and every time, then passing back the description is not necessary. This is exactly the kind of thing I will be doing in the future. John W. Colby www.ColbyConsulting.com Shamil Salakhetdinov wrote: > 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 > > > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > > _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 4641 (20091127) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru __________ Information from ESET NOD32 Antivirus, version of virus signature database 4641 (20091127) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru