Return bigint from SQL Server stored procedure

Question

I have written a stored procedure in SQL Server which will return a Bigint value

alter procedure [dbo].adding 
    @one bigint,
    @two bigint,
    @startid bigint output 
as
begin
    set @startid = @one + @two

    return @startid     
end

But while returning value I am getting an exception

Arithmetic overflow error converting expression to data type int

Can any one please help me to solve this issue?

Thanks in advance

Note : Above query is not the exactly the same procedure which i am using

UPDATED :

I have a code like below

_lookupId = cmdInsert.Parameters.Add("RetVal", SqlDbType.BigInt);
                        _lookupId.Direction = ParameterDirection.ReturnValue;

                        _procIn01 = cmdInsert.Parameters.Add("@idCount", SqlDbType.VarChar, 500);
                        cmdInsert.Parameters["@idCount"].Value = idCount;
                        _procIn01.Direction = ParameterDirection.Input;

                        _procIn02 = cmdInsert.Parameters.Add("@requestFrom", SqlDbType.VarChar, 100);
                        cmdInsert.Parameters["@requestFrom"].Value = clientId;
                        _procIn02.Direction = ParameterDirection.Input;

                        _pramOut = cmdInsert.Parameters.Add("@startID", SqlDbType.BigInt);
                        _pramOut.Direction = ParameterDirection.Output;
                        cmdInsert.ExecuteScalar();

With out returning the value how can i assign the value to "RetVal" my _lookup variable.

You don't actually have to return your output var, just setting it should be enough for the output to be populated for your app — Kritner, Sep 12 '14 at 12:25

Gordon Linoff · Accepted Answer · 2014-09-13T14:16:17.313

7

Procedures return a status value, which is always an integer. You can return the value from the stored procedure just by setting it:

alter procedure [dbo].adding 
    -- add the parameters for the stored procedure here
    @one bigint,
    @two bigint,
    @startid bigint output 
as
begin
    -- set nocount on added to prevent extra result sets from
    -- interfering with select statements.

    set @startid = @one + @two     
end;

Use return for whether or not the stored procedure succeeds.

You could call this with something like:

declare @startid bigint;

exec dbo.adding(1, 2, @startid output);

select @startid;

edited Sep 13 '14 at 14:16

answered Sep 12 '14 at 12:14

Gordon Linoff

1,242,037
58
646
786

@Backtrack . . . Here is an example of how to write the code in C#: http://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp. – Gordon Linoff Sep 12 '14 at 13:26

Mehdi Haghshenas · Answer 2 · 2014-09-12T18:59:57.670

0

This error occurred because your return but you must using set to set a variable or using select

Set @outputvalue=@val1+@val2

Or

Select @outputvalue=@val1+@val2

edited Sep 12 '14 at 18:59

answered Sep 12 '14 at 14:45

Mehdi Haghshenas

2,433
1
16
35

Mehdi Haghshenas · Answer 3 · 2020-04-30T13:39:21.300

0

Sotred procedure return answer is integer number that show sp successed or not
you can not use it for getting an big int number if you want get a output value with out output parameter then you can use select with out variable, for example,
calling

Select @val1+@val2

now for get output using Execute Scalar method of command.

edited Apr 30 '20 at 13:39

answered Sep 12 '14 at 19:05

Mehdi Haghshenas

2,433
1
16
35

Return bigint from SQL Server stored procedure

3 Answers3