Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Output Parameters Expand / Collapse
Author
Message
Posted Friday, November 21, 2003 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #18496
Posted Tuesday, November 25, 2003 8:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 12, 2013 12:05 PM
Points: 110, Visits: 23
Wish you had included a few examples of retrieving the output values from ASP, VBS, js.





Post #88349
Posted Wednesday, November 26, 2003 7:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #88350
Posted Wednesday, November 26, 2003 7:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 9:01 AM
Points: 6,705, Visits: 1,680
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
Post #88351
Posted Thursday, November 25, 2004 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, Visits: 35

do not understand

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

maybe you can explain a little bit.




Post #148110
Posted Thursday, October 25, 2007 11:04 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 8:30 AM
Points: 769, Visits: 191
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?
Post #415218
Posted Thursday, October 25, 2007 11:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: Banned Members
Last Login: Wednesday, February 19, 2014 3:34 AM
Points: 2,559, Visits: 326
sunjiulu

do not understand
SELECT @intValue AS [@intValue], @strValue AS [@strValue], @bitValue AS [@bitValue]
maybe you can explain a little bit.


try these links talks about more on your stufff....


http://www.sqlservercentral.com/articles/Stored+Procedures/outputparameters/1200/
http://www.webtropy.com/articles/SQL.asp?SQL=sp_change_subscription_properties
Post #415227
Posted Friday, October 26, 2007 1:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 06, 2012 7:54 AM
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.
Post #415262
Posted Friday, October 26, 2007 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 06, 2008 10:12 PM
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.
Post #415271
Posted Friday, October 26, 2007 5:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 15, 2009 12:43 AM
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
Post #415300
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse