[RESOLVED] Collect a returned value from SP

[goluguddu]

Hi all

I have a Stored Procedure (SQL server 2000) that returns a Integer Value. i am calling the Sp from VB.Net code. How Do i collect the returned value.

Any suggestion!!

Thanks.

[eranfox]

Hello goluguddu,
I don't know DOT NET yet but i know how to do this in vb6.
There is a small example in SQLServer books on-line:
search under ADO and SQL Server the title:
"Using Return Code and Output Parameters for Stored Procedures"

but here is the code anyway:

The following stored procedure contains one input parameter, one output parameter, and a return parameter. The procedure selects those rows in the titles table of the pubs database where the royalty percent paid to the author is greater than the amount entered by the user (the input parameter). The program returns the number of rows as the output variable. If the program returns any rows, a return code of 0 is issued; if no rows are returned, a return code of 99 is issued.

USE pubs
GO
CREATE PROCEDURE myProc
@outparm      int      OUTPUT
@inparm      int
AS
SELECT * FROM titles WHERE royalty > @inparm
SELECT @outparm = COUNT (*) FROM TITLES WHERE royalty > @inparm
IF (@outparm > 0)
RETURN 0
ELSE
RETURN 99
GO

An ADO code program that executes the stored procedure myProc is shown here.

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim fldloop As ADODB.Field
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim provStr As String
Dim royalty As Variant
    
Private Sub spStart()
 
' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"
 
' Specify connection string on Open method.
provStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes"
cn.Open provStr
 
' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cn
cmd.CommandText = "myProc"
cmd.CommandType = adCmdStoredProc
 
' Set up a return parameter.
Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append param1
            
' Set up an output parameter.
Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput)
cmd.Parameters.Append param2
  
' Set up an input parameter.
Set param3 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append param3
royalty = Trim(InputBox("Enter royalty:"))
param3.Value = royalty
 
' Execute command, and loop through recordset, printing out rows.
Set rs = cmd.Execute
 
Dim i As Integer
While Not rs.EOF
    For Each fldloop In rs.Fields
        Debug.Print rs.Fields(i)
        i = i + 1
    Next fldloop
    Debug.Print ""
    i = 0
    rs.MoveNext
Wend
 
' Need to close recordset before getting return 
' and output parameters.
rs.Close
 
Debug.Print "Program ended with return code: " & Cmd(0)
Debug.Print "Total rows satisfying condition: " & Cmd(1)
cn.Close
 
End Sub

Best Regards,
ERAN

[techgnome]

For ADO.NET that code is useless. If you are using an output parameter, and not also returning a recordset, create the connectioon, the command, add the parameters, then use .ExecuteNoQuery. After than, check the proper parameter, and the value should be there.

If you want to capture it ala a RETURN from SQL, there are two ways. 1 - use the same parameter method above, BUT the return parameter MUST be the first parameter added, and it has to be of direction type Return Value. The second assumes no putput parameters and no recordset is returned either. And that's to use the .ExecuteScalar function.

-tg

[goluguddu]

For ADO.NET that code is useless. If you are using an output parameter, and not also returning a recordset, create the connectioon, the command, add the parameters, then use .ExecuteNoQuery. After than, check the proper parameter, and the value should be there.

If you want to capture it ala a RETURN from SQL, there are two ways. 1 - use the same parameter method above, BUT the return parameter MUST be the first parameter added, and it has to be of direction type Return Value. The second assumes no putput parameters and no recordset is returned either. And that's to use the .ExecuteScalar function.

-tg

Hi Thanks for you reply. I dont have output variable in the Sp. Please suggest me the changes in the following code.

CREATE PROCEDURE [dbo].[ins_Template]
@vchar_TemplateName varchar(100),       -- Template Name
@int_CustomerId int,            -- Customer Id
 
-- INSERT
INSERT INTO dbo.map_Templates (TemplateName, CustomerId)
VALUES  (@vchar_TemplateName, @int_CustomerId)
 
SET @int_TemplateId = @@IDENTITY
 
RETURN @int_TemplateId
GO
 
 
SQL = "EXEC ins_Template " & _
                      "@vchar_TemplateName = '" & Name & "'," & _
                      "@int_CustomerId = " & CustomerID
 
Dim UCommand As SqlClient.SqlCommand = New SqlCommand(SQL, BlitzKarte4.GlobalConnection)
 
UCommand.ExecuteNonQuery()

Thanks.

[techgnome]

Change

UCommand.ExecuteNonQuery()

to


RetCode = UCommand.ExecuteScalar()

Where RetCode is your variable where you want top store the return value.

-tg

[goluguddu]

It is not working. The return value I get is not correct.

[techgnome]

And that's because I missed how you were constructing your SQL....

UCommand.Parameters.Add(New SqlClient.SqlParameter("@vchar_TemplateName", SqlDbType.VarChar, 100))
        UCommand.Parameters.Add(New SqlClient.SqlParameter("@int_CustomerId", SqlDbType.Int))
 
        UCommand.Parameters("@vchar_TemplateName").Value = Name
        UCommand.Parameters("@int_CustomerId").Value = CustomerID
 
        MyResult = UCommand.ExecuteScalar()

-tg

[goluguddu]

Hi thanks alot for help..but still its not working. Here is the code I am running....

SQL = "EXEC ins_Template " & _
                      "@vchar_TemplateName = '" & Name & "'," & _
                      "@int_CustomerId = " & CustomerID
 
Dim UCommand As SqlClient.SqlCommand = New SqlCommand(SQL, BlitzKarte4.GlobalConnection)
 
UCommand.Parameters.Add(New SqlClient.SqlParameter("@vchar_TemplateName", SqlDbType.VarChar, 100))
        UCommand.Parameters.Add(New SqlClient.SqlParameter("@int_CustomerId", SqlDbType.Int))
 
        UCommand.Parameters("@vchar_TemplateName").Value = Name
        UCommand.Parameters("@int_CustomerId").Value = CustomerID
 
        MyResult = UCommand.ExecuteScalar()

[techgnome]

Garrgh.... (that was directed at myself, I left off some info)....

I meant to preface that with this:
Don't use that for your SQL. Your CommandText should be just the name of the stored procedure and nothing else. Also be sure to set the CommandType to StoredProcedure before you execute it (idealy right after you set the command text)

-tg

[goluguddu]

well...Its not working(i=0).....Dont I need to add returned parameter "@int_TemplateId" to the UCommand as well.

The code is....

Dim i as integer
UCommand.CommandText = "ins_Template"
UCommand.CommandType = CommandType.StoredProcedure
UCommand.Connection = BlitzKarte.GlobalConnection
 
UCommand.Parameters.Add(New SqlClient.SqlParameter("@vchar_TemplateName", SqlDbType.VarChar, 100))
UCommand.Parameters.Add(New SqlClient.SqlParameter("@int_CustomerId", SqlDbType.Int))
 
UCommand.Parameters("@vchar_TemplateName").Value = Name
UCommand.Parameters("@int_CustomerId").Value = CustomerID  
 
i = UCommand.ExecuteScalar()
UCommand.Dispose()

[techgnome]

no, because of this line in the SP: RETURN @int_TemplateId

It returns the int_TemplateID tthrough the sP.... try thids....

Dim i as integer
UCommand.CommandText = "ins_Template"
UCommand.CommandType = CommandType.StoredProcedure
UCommand.Connection = BlitzKarte.GlobalConnection
 
UCommand.Parameters.Add(New SqlClient.SqlParameter("@int_TemplateID", SqlDbType.VarChar, 100))
UCommand.Parameters.Add(New SqlClient.SqlParameter("@vchar_TemplateName", SqlDbType.VarChar, 100))
UCommand.Parameters.Add(New SqlClient.SqlParameter("@int_CustomerId", SqlDbType.Int))
 
UCommand.Parameters("@int_TemplateID").Direction =ParameterDirection.ReturnValue 
UCommand.Parameters("@vchar_TemplateName").Value = Name
UCommand.Parameters("@int_CustomerId").Value = CustomerID  
 
UCommand.ExecuteNonQuery()
i= UCommand.Parameters("@int_TemplateID").Value
 
UCommand.Dispose()

Then change your SP like this:

CREATE PROCEDURE [dbo].[ins_Template]
@vchar_TemplateName varchar(100),		-- Template Name
@int_CustomerId int,			-- Customer Id

-- INSERT
INSERT INTO dbo.map_Templates (TemplateName, CustomerId)
VALUES	(@vchar_TemplateName, @int_CustomerId)

SET @int_TemplateId = @@IDENTITY

RETURN @int_TemplateId
GO

-tg

[goluguddu]

Awesom....It works. Thanks a lot.
Great job!!!