Output Parameters

  • John Masciantoni

    Mr or Mrs. 500

    Points: 503

    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

    SSCarpal Tunnel

    Points: 4454

    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

    SSCarpal Tunnel

    Points: 4259

    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.

    SSCrazy

    Points: 2801

    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

    SSCarpal Tunnel

    Points: 4259

    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

    Grasshopper

    Points: 21

    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.

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    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?

  • Grant Fritchey

    SSC Guru

    Points: 396692

    jpatevans4651 (12/24/2008)


    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.

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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