How do you access the Return value from a Sql Server Stored Procedure 1

[WantsToLearn]

Hello All,

From where do you retrieve the Return value from a stored procedure in Sql Server? Thanks!

 

[WantsToLearn]

Found the answer after doing a different search.

cmd.Parameters.Add("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

 

[RustyAfro]

I am really struggling with this. I understand how to use the cmd.parameters, but I don't know how to write the stored procedure correctly in the server.

I know that the code below works, and I have succesfully passed a value from vb.net to @MyValue. But I cannot figure how to write the SQL to declare a return variable and call it in vb.net and "read" it (or msgbox it). The best I have been able to do is use "executescaler" on a command to get the first row back, which is cool, but I want to actually reference a return value so I can use executereader instead.

CREATE PROC udp_Test
@MyValue
AS
Select * 
From MyTable
Where = @MyValue

If you can give a stored procedure example along with vb.net code to get the SQL @variable into a VB.net variable I would really appreciate it =)

 

[chmohan]

CREATE PROC udp_Test
@MyValue int
AS

Select returnvariablename as nameiwant
From MyTable
Where ----= @MyValue

and nameiwant is the variable you will get into ur vb.net code

 

[jebenson]

Take a look at this thread: thread796-979921
it may help you out.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[RustyAfro]

Actually both of you have helped, thank you very much =). I have more questions though. Jebenson, in that thread I see a few things that are confusing.

He uses "@DateParam DATETIME out" before AS, then assigns @DateParam to a field in the table. I tried duplicating that in the below T-SQL stored procedure, but I get an error stating that I must declare the variable "@Email", which I thought I was doing?

CREATE PROCEDURE dbo.sp_GetUserEmailTest
@UserID char(7)
@Email varchar(50) Out
AS
Select @Email = Email
From tbl_Users
Where UserID = @userID

What am I doing wrong?

 

[RustyAfro]

Ack, I missed the comma after the first variable, let me keep trying now.

 

[RustyAfro]

Ok, I got everything to work in VB.net with the stored procedure except for the "Return Select @@ROWCOUNT". If i Put Return 1, I can get the "1" value in vb.net, but "Return Select @@ROWCOUNT" returns "0". I pasted the solution in case anyone else gets stuck like I did. How can I get the rowcount into a variable?

SQL:

CREATE PROCEDURE dbo.sp_GetUserEmailTest
(
@UserID char(7),
@Email varchar(50) Out
)
AS
Select @Email = Email
From tbl_Users
Where UserID = @userID

return 1
GO

Vb.net:

Public Const SQL_Connection As String = "Server=ServerPath;DataBase=Database;Trusted_Connection=Yes"

Function GetUserEmail()

        Dim cnn As SqlConnection = New SqlConnection(SQL_Connection)
        Dim cmd As SqlCommand = New SqlCommand
        Dim result As String

        Try
            With cmd
                .Connection = cnn 'Connect to the SQL Server
                .CommandType = CommandType.StoredProcedure 'Make the command a stored procedure call
                .CommandText = "sp_GetUserEmailTest" 'The name of the stored procedure

                .Parameters.Add("@userID", SqlDbType.Char, 7) 'The parameter the query needs
                .Parameters("@userID").Direction = ParameterDirection.Input
                .Parameters("@userID").Value = gUser.ID 'Pass the users employee ID to the stored procedure

                .Parameters.Add("@email", SqlDbType.VarChar, 50)
                .Parameters("@email").Direction = ParameterDirection.Output

                .Parameters.Add("@Return_Value", SqlDbType.Int)
                .Parameters("@Return_Value").Direction = ParameterDirection.ReturnValue

                Try
                    'Open the connection
                    cnn.Open()
                    'Run the command
                    .ExecuteReader()
                    'Close the connection as soon as possible
                    cnn.Close()
                Catch ex As Exception
                    MessageBox.Show("Failed to connect to the database!" & ControlChars.CrLf & ex.Message)
                End Try

                MsgBox(.Parameters("@email").Value)
                MsgBox(.Parameters("@return_value").Value)              
            End With
        Catch ex As Exception
            MessageBox.Show("Failed to get the email!" & ControlChars.CrLf & ex.Message)
        End Try
    End Function

 

[RustyAfro]

Figured it out:

Result @@ROWCOUNT

On a roll!

 

[RustyAfro]

err Return @@ROWCOUNT (not result)