September 15, 2003 at 8:45 am
I had an interesting dicussion over these with one of the developers last week over the merits or otherwise of OUTPUT parameters.
In the past my previous employers have always preferred to use resultsets or to RAISEERROR - because of this I tend to go the same route.
what are other people views?
September 15, 2003 at 10:06 am
I tend to use a result set, keep things consistent, but no real reason why output parameters don't work. They've confused developers at times, so I stick with what they're used to.
RAISERROR is nice because it throws a flag for an application. Nice for errors since you can't always catch them in TSQL.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 15, 2003 at 1:32 pm
I used results sets more when interfacing with ASP because I didn't initially learn the ADO objects. Within stored procedures (One calling another) I much prefer the OUTPUT parameters. In my current environment (C# .NET), I like the SqlCommand object and use OUTPUT parameters wherever I can because they are more efficient and we have very high volume. Obviously, result sets are the only option for multiple rows.
As an aside - I never use RAISEERROR. Because of our N-tier implementation with multiple client languages, the SQL connection doesn't have enough information. We just pass back a code to be interpreted by our custom error handling routines.
Guarddata-
September 15, 2003 at 4:42 pm
I use output parameters when I KNOW that there is only going to be one value returned. Otherwise I use resultsets. There is a lot of overhead to go along with the resultset so if you can use the output parameter instead you will see better performance. IE: if you are creating a customer id then use an output parameter. If you need to get a list of customer ids use a resultset.
I use RAISERROR in all my SP's. Any time I am doing something that might throw an error I check it and do a RAISERROR. In many cases I have also added the error messages to the server so that the error can be displayed better.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 16, 2003 at 5:47 am
Nice to see all your feedback.
Whilst I can see there may be a little extra overhead in returning results where there is one row, I would consider it 'minimal', unless of course its repetively called in batch. In that instance the overhead may mount up.
This came up primarily because the developer insists on using output params to return the single row recordset, whilst I see them more a an aid to returning indicators of success/status.
Of course, like most things here, its a matter of personal preference though frankly I find output params a pain in the ass to deal with in Query Analyzer.
September 16, 2003 at 6:10 am
I prefer to use OUTPUT parameters when specifically return a single item such as a Primary Key value based on an identity. It does save network chatter but also requires you to setup your ADO connection properly to avoid a resultset.
As for RAISERROR I use it quite frequently to do validation and return my specific wording instead of a generic message. For example I do a lot of code where the PK value is submitted by user input and I don't want them to see the generic duplicate message so I use and EXIST in the SP and return "Record already exists for blah." instead. I just feel I have more control.
September 16, 2003 at 8:43 am
Spireite - one more habit we have is that all of our stored procedures have a flag "@PrintMe". The default is true for reporting procedures and false for data manipulations. This allows seeing the result set when the procedure is run from QA even though the procedure may be used in production with the OUTPUT params.
Guarddata-
September 16, 2003 at 10:16 am
I am wondering why nobody mentioned UDF's. Since we can get a returned value as a table or scalar value .....
September 16, 2003 at 11:45 am
spireite,
While building recordsets for a single record may seem trivial. As soon as you multiply that creation over all the conceivable connections a web server could have it can really add up fast.
I've used the idea of the '@printme' flag many times although I always called it '@Debugging' and use it to help in debugging certain areas in Query Analyzer. 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 16, 2003 at 12:25 pm
quote:
I am wondering why nobody mentioned UDF's. Since we can get a returned value as a table or scalar value .....
Because the conversation was about SPs and no mention of what SQL Version is made. 7 doesn't support UDF's.
September 19, 2003 at 5:08 am
The approach adopted by me is to return resultsets when multiple rows need to be returned. If single row needs to be returned, then I generally return a resultset too. Whenever a single value needs to be returned I use an OUTPUT parameter. If the stored procedure gets information from different tables, and the values returned from executing one statement affect which statement is executed next (i.e. IF value = x THEN this ELSE that), then I use OUTPUT parameters to save multiple round trips to the server.
I have recently started to use RAISERROR to return error messages in English, so that developers have error messages that provide them with something a little more specific and helpful than the generic error messages. This is especially helpful in an n-tier application where the error may finally be thown as an exception several layers up from the data access layer.
Keith Grimsey BSc MBCS CEng
Senior Software Engineer
Keith
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply