Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Output Parameters


Output Parameters

Author
Message
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

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



Robert W. Marda
SQL Programmer
Ipreo

JoeBelson
JoeBelson
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

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



Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
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
SQL Programmer
Ipreo

Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679
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
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 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
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 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
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17597 Visits: 32267
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