SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Output Parameters


Output Parameters

Author
Message
Robert W Marda
Robert W Marda
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5867 Visits: 140
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/outputparameters.asp

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
JoeBelson
JoeBelson
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 23
Wish you had included a few examples of retrieving the output values from ASP, VBS, js.



Robert W Marda
Robert W Marda
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5867 Visits: 140
Unfortunately, I don't know ASP nor js. As for VBScript, my focus has not been on getting data from SQL Server. This past week I began learning ColdFusion. I intend to learn ASP also since the environment I was given at work to learn ColdFusion also supports ASP, however I have not began yet with ASP.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Andy Warren
Andy Warren
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: Moderators
Points: 48344 Visits: 2788
Not much difference between VBS and ASP. As far as using output parameters, it works the same as plain ADO from VB...except!

- You have to declare the constants in code or just use the numerical value for the consts. Not quite true for ASP, you can include a const file or set a reference (I think).

- Nothing is strongly typed. You still build a parameters collection, just cant type it that way.

If you have access to VB6, IMO you're better off to encapsulate as much of it as you can in a dll. It's compiled code, it's strongly typed, and you keep your data access out of the presentation layer.

I've got some articles posted on ADO if you need some examples of that.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
sunjiulu
sunjiulu
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 35

do not understand

SELECT @intValue AS [@intValue], @strValue AS [@strValue], @bitValue AS [@bitValue]

maybe you can explain a little bit.





jereme.guenther@gmail.com
jereme.guenther@gmail.com
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2277 Visits: 194
So what is the advantage of OUTPUT parameters over a standard result set? From my experience they are much more of a pain to deal with both in coding and in maintenance. I don't see how they could be faster from the SQL side since the sproc needs to finish executing one way or the other.

The only advantage I can see is that .NET (or whatever language you are using) doesn't need to instantiate another object (like the datareader or dataset) for such a small amount of data.

Once I have gone down the path of OUTPUT parameters I have a couple extra places that code has to be updated every time I want to return an additional value. I have to pass in the new parameter, I have to add it to the sproc, I have to assign it in the sproc, and I have to retrieve it in .NET.

If I am not using OUTPUT parameters then I simply add a new value to the select list in the sproc and add one more line in .NET to retrieve it.

My final problem with OUTPUT parameters is that I can only return single values with them, so if I ever decide to change the data call to a two or three record result set I have to rewrite everything pertaining to the data retrieval call. So I can see in an area where speed to the last millisecond is absolutely essential they could be a really good thing, but other than that they seem like a bit of a pain.

Am I missing something here? What are your thoughts on this?
Andrew la Grange
Andrew la Grange
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 15
I've got to agree with Jereme. I'm currently working on a legacy system which is FULL of OUTPUT Parameters (which sadly more often than not contain nothing of use on a regular basis, but that is more an architectural error), and it is a major pain, especially when wanting to quickly run an SP and you don't care/need the output param vars.

I read the article hoping there would be some sort of justification, or special case where OUTPUT params where the way to go... and I'm still left wondering. The only thing I can think of so far, is that its a legacy thing from when servers where a lot slower, and bandwidth a lot smaller than now. Returning a recordset (or more) seems so much simpler and more efficient. Yes, you might save some space on the transmission, and yes you wont have to instantiate an object on the client side, but considering the sheer amount of programming time you have to do to correctly handle OUTPUT params it almost doesn't seem worth the waste.

As I see it, the PROs are:
* Doesn't require recordset initialization
* Light in transmission
* Rigid Access to the SP [this is also a con]

Cons:
* Rigid Access to the SP
* Code heavy on client and server side
* More difficult to adjust dynamically

I'm left to think that OUTPUT params are a hangover from a "quieter, gentler time" of SQL Server development, and the fact that extensive use of it (in my experience) seems to be limited to older systems (and older sql programmers) suggests this to true.

But I'm really keen for someone to tell me how great OUTPUT params are, and how I'm missing the point; because if they are handy I'd love them to add them to my toolbelt. But at the moment they seem like a hammer for a screw.
Logan A
Logan A
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 8
I don't tend to use them with .NET but do like using output parameters when having stored procedures run another (sub) stored procedure type module and a large record set is not required.

Basically, in a way similar to creating / running a UDF but without the performance issues.

UDF's of course are functionally simpler to code with and use... but its just not worth it.
Jakub Poskrobko
Jakub Poskrobko
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 25
We have implemented static, auto-generated C# DB Layer, that deals with all parameters in such way:

for SP: exec mysp @par1 = @par1, @par2 = @par@

we just call (generated) C# function:
Exec_mysp (par1, par2);

so, for out params i just write:

int par1, par2;

Exec_mysp (out par1, out par2).

Autogenerating Exec_mysp function solves the problem if parameters list changes.

In such solution - i always use out variables (even for select TOP 1 a,b,c from my_tab); and i think that works fine.

Another point: not only store procs can be executed from C# (and other) using parameteres (and output params); it can be usual statement as well.

kuba
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202332 Visits: 33408
Jereme Guenther (10/25/2007)
So what is the advantage of OUTPUT parameters over a standard result set? From my experience they are much more of a pain to deal with both in coding and in maintenance. I don't see how they could be faster from the SQL side since the sproc needs to finish executing one way or the other.

The only advantage I can see is that .NET (or whatever language you are using) doesn't need to instantiate another object (like the datareader or dataset) for such a small amount of data.

Once I have gone down the path of OUTPUT parameters I have a couple extra places that code has to be updated every time I want to return an additional value. I have to pass in the new parameter, I have to add it to the sproc, I have to assign it in the sproc, and I have to retrieve it in .NET.

If I am not using OUTPUT parameters then I simply add a new value to the select list in the sproc and add one more line in .NET to retrieve it.

My final problem with OUTPUT parameters is that I can only return single values with them, so if I ever decide to change the data call to a two or three record result set I have to rewrite everything pertaining to the data retrieval call. So I can see in an area where speed to the last millisecond is absolutely essential they could be a really good thing, but other than that they seem like a bit of a pain.

Am I missing something here? What are your thoughts on this?


I think you've covered everything correctly. The one advantage I see is that in instances where you know that you're only ever going to return very small sets of data, say an ID value from a single row insert procedure, then the amount of work done by the server is reduced because sending the output parameter requires a little less processing than marshalling a result set. You also save a touch on network traffic because the output parameter is going to be smaller.

However, these are tiny, incremental, performance increases, realized after you've already squeezed everything you can out of the system. I wouldn't recommend implementing output parameters everywhere, but they do have a place and using them or not depends on the situation.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search