How to rewrite a function in SQL?

Question

SQL

Good afternoon, so, straight to the point.
You need to rewrite the SQL function.
What is required of it: it accepts the @in_CustomerID parameter (CustomerID in the LoginID table), it contains a user ID of up to 32 digits, in the same LoginID table there is a GamePoints field, using the CustomerID pointer, you need to find the user's balance and display the figure as a final result GNABalance.
Dropped the original version below.
ALTER PROCEDURE [dbo].[WO_GNAGetBalance]
@in_CustomerID int
AS
BEGIN
SET NOCOUNT ON;
-- gna service id for warinc
declare @ServiceId bigint = 300005010000000000
get points for that customer
declare @CustomerID bigint
select @CustomerID=CustomerID from LoginID where [email protected]_CustomerID
if (@@RowCount = 0) begin
select 6 as ResultCode, 'no CustomerID' as ResultMsg
return
end
declare @AuthId varchar(40)
set @AuthId = convert(varchar(32 ) ), @CustomerID)
-- get balance from gamenet
DECLARE @o_Balance money;
DECLARE @o_Error int;
SET @o_Balance = NULL;
SET @o_Error = NULL;
EXEC dblink_api.billing.dbo.usp_GetBalance
@i_ServiceId = @ServiceId,
@i_AuthType = 'user_id',
@i_AuthId = @AuthId,
@o_Balance = @o_Balance out,
@o_Error = @o_Error out;
if(@o_Error > 0) begin
select 6 as ResultCode, 'can not get balance' as ResultMsg
return
end
--convert NULL to 0
if(@o_Balance is NULL) set @o_Balance = 0
declare @Balance int = convert(int, @o_Balance)
select 0 as ResultCode
select @Balance as 'GNABalance'
END

Answer 1

On the application side, receiving a request looks like this:
int CClientUserProfile::ApiGNAGetBalance()
{
if(!RUS_CLIENT)
r3dError("ApiGNAUpdateBalance");
CWOBackendReq req(this, "api_GNAGetBalance.aspx");
if(!req.Issue())
{
r3dOutToLog("GNAUpdateBalance FAILED, code: %d\n", req.resultCode_);
return req.resultCode_;
}
int balance = 0;
int nargs = sscanf(req.bodyStr_, "%d" , &balance);
if(nargs != 1)
{
r3dError("wrong answer for GNAUpdateBalance");
return 9;
}
ProfileData.Stats.GamePoints = balance;
return 0;
}
That is, the @Balance variable with data from GamePoints should go to the subd

Answer 2

I would write something like this:

create procedure [dbo].[WO_GNAGetBalance]
  @in_CustomerID int
, @balance       money        output  
, @result_code   int          output
, @result_msg    varchar(255) output
as
begin
set nocount on

if not exists (select top 1 * from LoginID where CustomerID = @in_CustomerID)
begin
  select @result_code = 6
       , @result_msg = 'no CustomerID'
  return 6
end

select @balance = sum(isnull(GamePoints, 0)) as GNABalance from LoginID
  where CustomerID = @in_CustomerID;
  
if @@error != 0
begin
  select @result_code = 7
       , @result_msg = 'Error get balance'
  return 7
end

select @result_code = 0
     , @result_msg = 'Success!'

return 0
end