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


Output Parameters


Output Parameters

Author
Message
Mike Dougherty
Mike Dougherty
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 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]
Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15916 Visits: 18777
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?
jereme.guenther@gmail.com
jereme.guenther@gmail.com
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1121 Visits: 194
"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.
Jason1972
Jason1972
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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.
John Masciantoni
John Masciantoni
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 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
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1928 Visits: 466
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.
jereme.guenther@gmail.com
jereme.guenther@gmail.com
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1121 Visits: 194
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.
Doug S.
Doug S.
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 333
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
jereme.guenther@gmail.com
jereme.guenther@gmail.com
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1121 Visits: 194
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.
jpatevans4651
jpatevans4651
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: 3
Hi,

Is there no limit to stored procedure parameters because when I use ado.net I get the following when trying to call a stored procedure with 12 parameters:
Procedure or function GetItem has too many arguments specified.

I know this isn't a .net board but figured others may have run into this.

thanks.
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