none
Output Parameter For Stored Procedure Failing RRS feed

  • Question

  • I have this for a stored procedure:

    USE [Numbers]
    GO
    /****** Object:  StoredProcedure [dbo].[thenextnum]    Script Date: 09/23/2009 15:35:16 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER procedure [dbo].[thenextnum]
         @descrip varchar(50)
    as

    begin

    declare @t table (nextseq varchar(100)) 

    update   number_definition_table
     set Current_Number=Current_Number+1
    output  inserted.prefix_format 
    +(Case WHEN inserted.leading_hyphen = 'NONE' then '' 
    ELSE inserted.leading_hyphen END
    )
    --+ inserted.leading_hyphen  
    +right( 
    replicate('0',inserted.Character_Count) 
    +convert(varchar(50),inserted.Current_Number)
    ,inserted.Character_Count)  
                   +(Case WHEN inserted.trailing_hyphen = 'NONE' then '' 
    ELSE inserted.trailing_hyphen END 
    )  into @t    
    where descriptor = @descrip
    select nextseq from @t 
    end

    When I use the VB code below to hand the value for @descrip and expect back the next number (@t) it fails with procedure has to many arguments at  myCommand.ExecuteNonQuery()
    If I comment out the

    Dim mynextnum As New SqlParameter("@t", SqlDbType.VarChar, 100, ParameterDirection.Output)

    the routine executes without error and the SP actually executes. This  VB code appears to be correct yet it will not hand back the value the SP does when executed manually in SSMS. 


     
            Dim conn As New SqlConnection("Data Source=CESIUM\SQLEXPRESS;User ID=sa;Password=right;")
            conn.Open()

            'set up the command object
            Dim myCommand As New SqlCommand("thenextnum", conn)
            myCommand.CommandType = CommandType.StoredProcedure

            'add the first two parameters
            myCommand.Parameters.Add("@descrip", SqlDbType.Text).Value = Descriptor_ComboBox.Text

            Dim mynextnum As New SqlParameter("@t", SqlDbType.VarChar, 100, ParameterDirection.Output)

            'add them to the parameter collection
            myCommand.Parameters.Add(mynextnum)

            'execute the query
            myCommand.ExecuteNonQuery()

            'display the values
            MsgBox(mynextnum.Value)
        End Sub


    • Edited by gedkins Thursday, September 24, 2009 12:57 AM error
    Thursday, September 24, 2009 12:56 AM

Answers

  • As Deborah mentioned, you don't have the output parameter defined in the stored procedure signature so that it can be returned from the call. You probably want something like the following:

    ALTER procedure [dbo].[thenextnum]
         @descrip varchar(50),
         @nextseq varchar(100) OUTPUT 'assuming it's the sequence number that you want to return
    as
    
    
    ...
    ...
    select @nextseq from @t

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by gedkins Wednesday, September 30, 2009 9:58 PM
    Wednesday, September 30, 2009 12:55 PM

All replies

  • My understanding is  that an output parameter would need to be defined on the parameter list of the stored procedure. (That is, with the @Descrip, before the "as") statement.

    What you have is a return value.

    Try setting the ParameterDirection.ReturnValue instead of output and see if that works.

    Hope this helps.
    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Wednesday, September 30, 2009 6:19 AM
  • As Deborah mentioned, you don't have the output parameter defined in the stored procedure signature so that it can be returned from the call. You probably want something like the following:

    ALTER procedure [dbo].[thenextnum]
         @descrip varchar(50),
         @nextseq varchar(100) OUTPUT 'assuming it's the sequence number that you want to return
    as
    
    
    ...
    ...
    select @nextseq from @t

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by gedkins Wednesday, September 30, 2009 9:58 PM
    Wednesday, September 30, 2009 12:55 PM