Returning bigint from SQL Server stored procedure

I wrote a stored procedure in SQL Server that will return a value Bigint

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

    return @startid     


But when I return the value, I get the exception

Arithmetic overflow error converting expression to int data type

Can anyone help me solve this problem?

Thank you in advance

Note. The above query is not the same procedure I am using


I have below code

_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;


Without returning a value, how can I assign the value to the "RetVal" variable to my _lookup variable.


source to share

3 answers

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

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

    set @startid = @one + @two     


Use the return

stored procedure to execute successfully.

You can call it something like:

declare @startid bigint;

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

select @startid;




This error came from a return, but you have to use set to set the variable or with select

Set @outputvalue=@val1+@val2



Select @outputvalue=@val1+@val2




The response to the response of the requested procedure is an integer indicating sp is successful or not. you cannot use it to get the number big int

if you want to get the output value without out put parameter

, then you can use select with the out variable, for example

Select @val1+@val2


now to get output using command Execute Scalar




All Articles