Stored Procedure Return

M

MTSkull

Well-known member
Joined
Mar 25, 2003
Messages
135
Location
Boulder, Colorado
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
Code: 
        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
Code: 
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
 
ADO.NET has built in methods of executing stored procedures. I think something like this should do it:

Code: 
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]
 
Last edited by a moderator:
You can also use the ExecuteScalar method of the SqlCommand object if the data type is an appropriate fit (boolean, integer, etc.).
 
Still Having a little trouble...
constants
Code: 
    Private Const PRIV_PARAM_RETURN_NAME = "ReturnValue"
    Private Const PRIV_PARAM_TABLE_NAME = "Table_Name"

and the function minus the error handler
Code: 
    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?
 
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
 
Back
Top