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, May 22 '16 at 08:31
Getting error "Cannot implicitly convert type 'object' to 'Sybase.Data.AseClient.AseParameter'. An explicit conversion exists (are you missing a cast?) — Frank Martin, May 22 '16 at 08:37

score 2 · Answer 1 · edited Jan 21 '22 at 18:56

2

Using the following code fixed the issue. I realized that I was passing SqlDbType.Int when this is actually Sybase ASE 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;

edited Jan 21 '22 at 18:56

Cᴏʀʏ

105,112
20
162
194

answered May 22 '16 at 10:44

Frank Martin

3,147
16
52
73

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

Sounds like you need a sqldatareader, given that you're hoping to get a value back.

This question has the code already written out for you. Just needs adapted to your case. There are also a few good links to some pretty decent learning materials.

edited May 23 '17 at 12:31

Community

1
1

answered May 22 '16 at 08:52

Travis

1,044
1
17
36

How get return value from Stored Procedure

2 Answers2