Stored Procedure Return

[MTSkull]

I have a stored procedure which gets an int value from a table increments it by 1 then returns the result to the user and stores the new result back into the originating table. The stored procedure seems to work fine but, how do I access the return?

 

I tried

       strConn = "data source=" & gstrServer & ";"
       strConn &= "initial catalog=" & gstrDB & ";"
       strConn &= "Integrated Security=SSPI"

       strSQL = "EXEC SP_Telesales_Return_Key "
       strSQL &= "'" & Table_Name & "'"

       sqlConnection = New SqlClient.SqlConnection(strConn)

       sqlCommand = New SqlClient.SqlCommand(strSQL, sqlConnection)

       'sqlCommand.()  '?now what???

 

Stored Procedure

CREATE PROCEDURE dbo.SP_Telesales_Return_Key(@Table_Name as varchar(100)) AS

DECLARE @Value AS INT
DECLARE @strReturn as varchar(50)

SET @Table_Name = ltrim(rtrim(@Table_Name))

SET @Value = (SELECT cast(Sys_Value as int) FROM Telesales_System WHERE Sys_Description = @Table_Name)

SET @Value = @Value + 1

UPDATE Telesales_System SET Sys_Value = cast(@Value as varchar(50)) WHERE Sys_Description = @Table_Name

RETURN @Value
GO

Thanks

Brian

[Volte]

ADO.NET has built in methods of executing stored procedures. I think something like this should do it:

 

'Your sqlConnection and sqlCommands should have already been created
       sqlCommand.Connection = sqlConnection

       sqlCommand.CommandType = CommandType.StoredProcedure
       sqlCommand.CommandText = "testproc"
       sqlCommand.Parameters.Add("retval", Nothing)
       sqlCommand.Parameters("retval").Direction = ParameterDirection.ReturnValue

       'change to the appropriate type
       sqlCommand.Parameters("retval").SqlDbType = SqlDbType.Int

       sqlCommand.ExecuteNonQuery()

       MessageBox.Show("The stored procedure returned " & sqlCommand.Parameters("retval").Value.ToString)

When you add a parameter to the Parameters collection and set the Direction to 'ReturnValue' it will contain the value returned by the stored procedure when it is complete.

 

[edit]I almost forgot to mention that you should specify the correct type for the return value[/edit]

Edited June 23, 2003 by Volte

[Derek Stone]

You can also use the ExecuteScalar method of the SqlCommand object if the data type is an appropriate fit (boolean, integer, etc.).

[MTSkull]

Still Having a little trouble...

constants

   Private Const PRIV_PARAM_RETURN_NAME = "ReturnValue"
   Private Const PRIV_PARAM_TABLE_NAME = "Table_Name"

 

and the function minus the error handler

   Public Function GetNewKey(ByVal Table_Name As String) As String
       On Error GoTo ErrorHandler

       Dim sqlCommand As New SqlClient.SqlCommand
       Dim sqlConnection As SqlClient.SqlConnection
       Dim strConn As String
       Dim strSQL As String

       'has the current key been used if so then return the current key
       If Not bUsed Then
           GetNewKey = strKey
           Exit Function
       End If

       strConn = "data source=" & gstrServer & ";"
       strConn &= "initial catalog=" & gstrDB & ";"
       strConn &= "Integrated Security=SSPI"

       strSQL = "SP_Telesales_Return_Key"

       sqlConnection = New SqlClient.SqlConnection(strConn)
       sqlConnection.Open()

       sqlCommand.Connection = sqlConnection

       sqlCommand.CommandType = CommandType.StoredProcedure
       sqlCommand.CommandText = strSQL

       'set Input parameter
       sqlCommand.Parameters.Add(PRIV_PARAM_TABLE_NAME, Table_Name)
       sqlCommand.Parameters(PRIV_PARAM_TABLE_NAME).Direction = ParameterDirection.Input

       'set Return parameter
       sqlCommand.Parameters.Add(PRIV_PARAM_RETURN_NAME, Nothing)
       sqlCommand.Parameters(PRIV_PARAM_RETURN_NAME).Direction = ParameterDirection.ReturnValue

       'change to the appropriate type
       sqlCommand.Parameters(PRIV_PARAM_RETURN_NAME).SqlDbType = SqlDbType.Int

       sqlCommand.ExecuteNonQuery()

       'return the key
       GetNewKey = sqlCommand.Parameters(PRIV_PARAM_TABLE_NAME).Value.ToString
       'store the class values for later use
       strKey = sqlCommand.Parameters(PRIV_PARAM_TABLE_NAME).Value.ToString
       bUsed = False

       Exit Function

 

I am getting a "Table_Name is not a parameter of the stored procedure" when I try to run this code against the Stored procedrure in the Original post. Do I need to change the Decleration in the Stored Procedure to pick up the input parameter or am I doing something else wrong?

[MTSkull]

Sorry to bug you guys but I fixed it. I just changed the input constant name from "Table Name" to "@Table_Name"

 

Thanks for all the great help.

Brian