Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Robert Marda » Output Parameters
Add to briefcase
23 posts, Page 2 of 3
««123»»

Output Parameters Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Grant Fritchey
Posted Friday, October 26, 2007 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,375, Visits: 25,159
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #415377
Mike Dougherty
Posted Friday, October 26, 2007 8:23 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110, Visits: 952
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]
Post #415423
Matt Miller (#4)
Posted Friday, October 26, 2007 8:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 6,998, Visits: 13,947
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?
Post #415442
Jereme Guenther
Posted Friday, October 26, 2007 9:25 AM
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: Saturday, December 03, 2011 10:08 AM
Points: 769, Visits: 187
"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.
Post #415471
Jason-262847
Posted Friday, October 26, 2007 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 10:14 AM
Points: 18, Visits: 167
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.
Post #415477
John Masciantoni
Posted Friday, October 26, 2007 11:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2008 10:03 AM
Points: 13, Visits: 20
Robert,
Excellent article. I have found many uses for the SQL Output but have one small problem.

Can anyone point me toward some example code that shows how to gather the output data when using VB.NET with late-binding in web applications. I use stored procs like crazy to return datasets but can't figure out how to get the OUTPUT items back into my program so I can do something with them. In the example below I would really like to just have the SP return an Output value of "true" or "false" and do away with all the extra check to see if a record came back or not.

Thanks.
John at Free Design

Public Function CheckUserLogin(ByVal vLoginName As String) As Boolean
Dim oSqlConn As Data.SqlClient.SqlConnection = SqlConnection()
Try
Dim oSqlCmdList As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("CheckUserLogin", oSqlConn)
oSqlCmdList.CommandType = Data.CommandType.StoredProcedure
Dim oSqlParm1 As New Data.SqlClient.SqlParameter("@UserLogin", vLoginName)
oSqlCmdList.Parameters.Add(oSqlParm1)
oSqlConn.Open()
Dim oSQLDataAdapter As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter
Dim oDataSet As Data.DataSet = New Data.DataSet
oSQLDataAdapter.SelectCommand = oSqlCmdList
oSQLDataAdapter.Fill(oDataSet, "ProcData")
'This checks for the existence of a login name
If oDataSet.Tables("ProcData") Is DBNull.Value Then
Return False
Else
Dim oDataView As Data.DataView = oDataSet.Tables("ProcData").DefaultView
If oDataView.Count <= 0 Then
Return False
Else
Return True
End If
End If
Catch oErr As Exception
WriteActivityLog(0, "SQL Error", "CheckUserLogin", oErr.Message)
SendErrorEmail("Help@MyFreeDesign.com", "SQLProc-NTO CheckUserlogin", oErr.Message)
Return False
Finally
If oSqlConn.State = Data.ConnectionState.Open Then
oSqlConn.Close()
End If
End Try
End Function
Post #415529
Mark D Powell
Posted Friday, October 26, 2007 12:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:26 PM
Points: 1,258, Visits: 341
It would have been nice if the start of the article contained a mention of to which versions of SQL Server the information is valid for. Many new DBA's may have to manage a shop where you can find verion 7.0, 2000, 2005, and soon 2008 all in use. Keeping straight what can be used where is a problem for new DBA's and part time SQL Server DBA's who work mainly with other databases.

I also hate scrolling right to read text. Scrolling to read code isn't too bad but trying to read text in 4 word chunks is annoying. I do not mind scrolling up and down to read text but right, no.

Any way fine article. Good blend on descriptive text and sample code.
Post #415581
Jereme Guenther
Posted Friday, October 26, 2007 1:41 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: Saturday, December 03, 2011 10:08 AM
Points: 769, Visits: 187
Sideout72 (10/26/2007)
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.



That does make sense. I find output parameters in functions to be cumbersome as well, though they have been useful on occasion.

Also this comparison is great for the functional side of things, but implementation wise it is much more of a pain to implement output parameters in a sproc than it is in a .NET function.
Post #415609
Doug S.
Posted Monday, October 29, 2007 12:35 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, September 21, 2011 8:43 AM
Points: 115, Visits: 315
for me, output parameters are most useful when your proc is an Insert on a table with an identity column. What you need is to pass back the Primary Key to the calling program. It works something like this

-- Insert statement goes here with @TableId as output parameter
create procedure dbo.MyProc (@TableId integer output)
as
Insert into TableX (xxx,xxx,xxx)
-- Put PK in @TableId
select @TableId = SCOPE_IDENTITY()
--
return @@error
--

A common programming mistake is that you don't close the recordset In your calling program. You need to do this before you can reference the output parameter in your code.

An example is provided here: http://msdn2.microsoft.com/en-us/library/aa905903(SQL.80).aspx

Doug


Doug
Post #416177
Jereme Guenther
Posted Monday, October 29, 2007 1:58 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: Saturday, December 03, 2011 10:08 AM
Points: 769, Visits: 187
I agree Doug, I have used them for that purpose as well.

Nice tip about having to close the record set before accessing output parameters, I didn't know that.
Post #416198
« Prev Topic | Next Topic »

Add to briefcase
23 posts, Page 2 of 3
««123»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.