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.
source to share
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;
source to share
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.
source to share