November 21, 2003 at 12:00 am
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
November 25, 2003 at 8:45 pm
Wish you had included a few examples of retrieving the output values from ASP, VBS, js.
November 26, 2003 at 7:34 am
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
November 26, 2003 at 7:52 am
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
November 25, 2004 at 6:52 am
do not understand
SELECT @intValue AS [@intValue], @strValue AS [@strValue], @bitValue AS [@bitValue]
maybe you can explain a little bit.
October 25, 2007 at 11:04 pm
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?
October 25, 2007 at 11:52 pm
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
October 26, 2007 at 1:55 am
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.
October 26, 2007 at 2:41 am
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.
October 26, 2007 at 5:00 am
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
October 26, 2007 at 7:21 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2007 at 8:23 am
We use JScript in the ASP environment, which to my knowledge does not support passing variables into the ADO object (like VBscript would allow) - so we must return a recordset in order to retrieve values from SQL. In the case of a simple insert, returning a recordset with the row Id is necessary. However, if that insert routine is called from another procedure the "row Id" recordset is probably not desired in the ADO results that the client (web server) is expecting. To manage multiple use of the insert procedure, I have been doing this:
create procedure spExample
@rowid int = null OUTPUT
,@returnRecordset bit = 0
as
begin
/* body of procedure */
set @rowid = scope_identity()
if @returnRecordset = 1
select [rowid] = @rowid
end
The Jscript caller over ADO uses: (needs a recordset)
execute spExample @returnRecordset = 1
Other stored procedures use: (do not pollute ADO results with extraneous recordset)
execute spExample @rowid = @newPKID OUTPUT
[Other likely parameters for an insert operation were omitted to simplify the example]
October 26, 2007 at 8:39 am
A few things:
- while .NET does make it relatively easy to go either way, it's not the only language. Most of the other solutions tend to have issues dealing with stored procedures returning more than one recordset, so if you need a recordset out of your stored proc AND something else, output parameters are going to become a convenient way to do that.
- If you want something new from a stored proc, you're going to need to change your code no matter what. I'm not sure that any one method is better than any other?
- Output parameters make it easy to pass info between stored procedures.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 26, 2007 at 9:25 am
"Output parameters make it easy to pass info between stored procedures."
That sounds like what I missed, I didn't realize they could be used between sprocs. That is really good to know, thanks; I can definitely see where they would be handy in the database layer.
October 26, 2007 at 9:41 am
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.
Think of using a stored procedure like you would use any fuction in traditional coding.
You have parameters that could be "in", "out", or "in/out" for a function. Think of OUTPUT parameters in a stored procedure like "in/out" for a function.
You also have a return value for a function. Think of a standard result set in a stored procedure like the return value for a function. Of course, that return value for a function could be multiple types - XML would be most like a standard SP result set.
Hope that helps.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply