How get return value from Stored Procedure

Question

I have an SP that returns integer value. The SP hasn't defined any return variable and it is directly returning value for e.g. return -1, return -2

I tried the following code but it gives exception:

Code

AseParameter retval = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int); retval.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery(); //This line throws exception

Exception

NotSupportException

BindParameter - Int

How to read return value?

EDIT

Stored Procedure

IF OBJECT_ID('dbo.MySP') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.MySP
    IF OBJECT_ID('dbo.MySP') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.MySP >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.MySP >>>'
END
go
create procedure MySP

    @param1         char(60),
    @param2         char(3),
    @param3           int,
    @param4         char(4)  
as
declare
    @somefield1          char(1),
    @somefield2           datetime,

    If @param1 is null
        return -1

    If @param2 != "L"
        return -2

more code here.....

Did you try to set a value? AseParameter retval = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Value = 0; — Steve
Getting error "Cannot implicitly convert type 'object' to 'Sybase.Data.AseClient.AseParameter'. An explicit conversion exists (are you missing a cast?) — Frank Martin

Frank Martin Frank Martin · Accepted Answer · 2016-05-22T10:44:18

Using following code fixed the issue. I realized that I was passing SqlDbType.Int when this is actually Sybase database.

AseParameter retval = new AseParameter("@RETURN_VALUE", AseDbType.Integer); cmd.Parameters.Add(retval); retval.Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); return (int)cmd.Parameters["@RETURN_VALUE"].Value;

How get return value from Stored Procedure

2 Answers