Calling a Stored Procedure that sends and returns values

[gedkins]

I have a stored procedure in SQL Server 2008 that follows:

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

It needs to get passed in a value from VB.net for the @descrip variable. This value represents a unique value found in the table for the field descriptor.
I need to get back the result from the @t variable which should be a sequential number that the stored procedure assembles. The stored procedure works as needed. What would the code in VB.net look like to pass in the value for @descriptor and accept back the value for @t.

Thanks,

[TomW]

Look up working with Stored Procedures and "Output Parameters"

[gedkins]

Look up working with Stored Procedures and "Output Parameters"

I have and unfortunately I am stuck with the issue of "Procedure or function thenextnum has too many arguments specified." I have worked this every way I can think of. If I leave out the code below the SP executes but I do not get back anything. I uncomment this and back to the same error.

I am baffled.

Dim myAnswer As New SqlParameter("@t", SqlDbType.VarChar, 30)
myAnswer.Direction = ParameterDirection.Output

[TomW]

The output parameter has to specified & declared in the actual stored procedure too. Also in the vb end, after execute the command you have to retrieve the value.

Create Procedure Blah

  @ReturnVal as Int Output,
  @Filter As VarChar(25)

Update tblBlah
Set...
Where Column = @Filter

@ReturnVal = Select Count(WhatEver) From Blah

cmd.Parameters("@ReturnVal").Direction = ParameterDirection.Output
 cmd.ExecuteNonQuery()
 intNewValue = CInt(.Parameters("@ReturnVal").Value)

[GeekByChoiCe]

if it need to return just a single value then it dont need to be declared as output.
here is an example:

Dim count As Integer

        Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)
            Dim myCommand As New SqlCommand("thenextnum", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            myCommand.Parameters.AddWithValue("@Filter", textbox1.Text)

            myConnection.Open()
            count = myCommand.ExecuteScalar 'assign the return
            myConnection.Close()
        End Using