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

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

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


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
+(Case WHEN inserted.trailing_hyphen = 'NONE' then ''
ELSE inserted.trailing_hyphen END
) into @t
where descriptor = @descrip
select nextseq from @t

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.


Recommended Answers

Look up working with Stored Procedures and "Output Parameters"

Jump to Post

All 4 Replies

Look up working with Stored Procedures and "Output Parameters"

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

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
Where Column = @Filter

@ReturnVal = Select Count(WhatEver) From Blah
cmd.Parameters("@ReturnVal").Direction = ParameterDirection.Output
 intNewValue = CInt(.Parameters("@ReturnVal").Value)

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)

            count = myCommand.ExecuteScalar 'assign the return
        End Using
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.