| Author |
Topic |
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
 Posted - 2004-04-17 : 15:47:30
|
Hello gals and guys. I trying to optimize my sprocs and was wondering what method is more efficient or provides better performance. I have several sprocs that add/update/delete data and in it I check for @@error like so:CREATE procedure rolyrolls.proc_AddInvoice @Number varchar (13)asbegin transaction <some action> if @@error <> 0 begin rollback tran return(1) endcommit transactionreturn(0)GO I return 1 for an error and 0 if everything is fine. Would it be better to use it as an OUTPUT parameter? Or is it fine just like that? And if so, I'm not familiar with calling a returned value in ASP classic, anyone got any examples?Thanks.- RoLY roLLs |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-04-17 : 16:28:50
|
| http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01b1.asp |
 |
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-17 : 17:55:55
|
| Thanks a bunch...looks helpful.- RoLY roLLs |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-17 : 18:15:12
|
| Ok I read the article and it's very good for some sprocs I have that return only one row of data. The problem with this article is that it compares returning a 1 row recordset vs output. In the case I specified above, I only return 1 value. Does the same rule apply? Has anyone done any tests similar to the article above? Is it a good idea if I need to return more than one value to use both return value and an output param, for example, i may want to have the return value set to either 1 (fail) or 0 (pass) and in the IF clause set an output variable to specify the error which occured?Thanks.- RoLY roLLs |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-21 : 12:19:16
|
| Hello? Anyone out there? Or is everyone paying attention to that "NewB Development Question" post? Hehe.- RoLY roLLs |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-04-21 : 21:51:48
|
| As a rule of thumb, if I want to return one value I use an output parameter, if I want a row I use a recordset (SELECT).That works for me, and I have data access code written to make that easy to work with. I would say find what you are comfortable working with, you might find the ADO.NET code to get a return value a bit of a pain to work with.Damian |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-04-21 : 21:56:19
|
| TSQL returns 0 automatically for success (when you haven't explicitly used the return method) and negative numbers for failures...I like to stick to that style myself....DavidM"If you are not my family or friend, then I will discriminate against you" |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-21 : 23:18:32
|
quote:
Originally posted by Merkin
...you might find the ADO.NET code to get a return value a bit of a pain to work with.
Hmm, something to think about, since I will be changing to ASP.NET soon enuff after I finish optimizing my sprocs. Must read up on this before I go on. I am at the point that I have no 'comfortable spot.' I try to see examples and change em and see what works for me. So before I get too used to this, if you have good documentation links on this, that would be greatful!Thanks.- RoLY roLLs |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-21 : 23:19:50
|
| byrmol, thanks. That sounds good if I wanted to be able to log some of these errors and store the type of error. Thanks.- RoLY roLLs |
|
|
|
|
Returned Value vs. OUTPUT