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 
as
begin
    set @startid = @one + @two

    return @startid     
end

      

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

UPDATED:

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;
                        cmdInsert.ExecuteScalar();

      

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

+3


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 
as
begin
    -- set nocount on added to prevent extra result sets from
    -- interfering with select statements.

    set @startid = @one + @two     
end;

      

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;

      

+6


source


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

Set @outputvalue=@val1+@val2

      



Or

Select @outputvalue=@val1+@val2

      

+1


source


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
call

Select @val1+@val2

      

now to get output using command Execute Scalar

command.

+1


source







All Articles