Output Parameters

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


    John at Free Design

    Public Function CheckUserLogin(ByVal vLoginName As String) As Boolean

    Dim oSqlConn As Data.SqlClient.SqlConnection = SqlConnection()


    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)



    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


    Dim oDataView As Data.DataView = oDataSet.Tables("ProcData").DefaultView

    If oDataView.Count <= 0 Then

    Return False


    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


    If oSqlConn.State = Data.ConnectionState.Open Then


    End If

    End Try

    End Function

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

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

  • 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)


    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



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

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


  • Andrew la Grange (10/26/2007)

    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'm not quite sure I understand that one. I run SP's with output parms all of the time without even givng them a parm to give back to me when I don't care about them.

    To me - the main reason to use output parms is for when you want a scalar value out of the procedure. The execution status is for just that (whether the SP execute correctly or not) and it just doesn't make sense to make a recordset out of that. Of course - it becomes a different question when you want FIFTY scalar values......

    EDIT: never mind - just noticed how old that was....:)

    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?

  • jpatevans4651 (12/24/2008)


    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.


    There is a limit, I don't recall what it is off the top of my head, but it's available in Books Online. It's something like 256 I think, so 12 is not it.

    That error means the procedure had 11, or less, parameters and you passed it too many.

    "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

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply