Overview

This page documents the list of stored procedures that MUST exist in your database for our API integration.

This page defines the contract for each stored procedure in terms of its name, input parameters, and expected output.

The API implementation will call these stored procedures to perform the necessary database operations for each API endpoint. It is crucial that the stored procedures are implemented according to the specifications outlined here to ensure seamless integration with our API.

API endpoint -> Stored Procedure Call -> Stored Procedure Outputs -> API Response -> Mobile App Results.

Stored procedure: dbo.blsmy_proc_getDepartments
SP Parameter Type Remarks
This procedure does not expect any input parameters 
CREATE PROCEDURE [dbo].[blsmy_proc_getDepartments]-- Name of the stored procedure.
AS
-- Your business logic to return active departments goes here.
-- Very important: Make sure to handle any potential errors and edge cases in your actual implementation.
Sample of returning an error message due to a wrong business logic:
	IF(any_error_logic)
	BEGIN
            SELECT 
                'Any-Code' AS ErrorCode,
                'Any-Message' AS ErrorMessageAR,
                'Any-Message' AS ErrorMessageEN;
               -- The error message text will be displayed directly in the mobile application to the patient, 
                    so make sure to provide clear and user-friendly messages in both Arabic and English.
        RETURN;
	END

-- Finally, return columns expected by the API mapper (Make sure the use the AS keyword for aliases).
-- Make sure all returned columns are of type nvarchar.
SELECT Id, DepartmentNameAR, DepartmentNameEN FROM your_table
Stored procedure: dbo.blsmy_proc_getDoctorsByDepartmentId
SP Parameter Type Remarks
@departmentId nvarchar(50) The department/Clinic Id. This is an optional parameter; if sent with null value; so the procdeure should return all the doctors in all departments. We use this procedures to sync the doctors list between your database and our database. 
CREATE PROCEDURE [dbo].[blsmy_proc_getDoctorsByDepartmentId]-- Name of the stored procedure.
@departmentId nvarchar(50) = NULL -- Ignore it from the WHERE clause if it's NULL.
as
-- Your business logic to return active departments goes here.
-- Very important: Make sure to handle any potential errors and edge cases in your actual implementation.
Sample of returning an error message due to a wrong business logic:
	IF(any_error_logic)
	BEGIN
            SELECT 
                'Any-Code' AS ErrorCode,
                'Any-Message' AS ErrorMessageAR,
                'Any-Message' AS ErrorMessageEN;
               -- The error message text will be displayed directly in the mobile application to the patient, 
                    so make sure to provide clear and user-friendly messages in both Arabic and English.
        RETURN;
	END

-- Finally, return columns expected by the API mapper (Make sure the use the AS keyword for aliases).
-- Make sure all returned columns are of type nvarchar.
-- Sample of a SELECT statement that returns the required columns:
                SELECT DoctorId, 
                DoctorCode, 
                DoctorNameAR, 
                DoctorNameEN, 
                DepartmentId, 
                DepartmentNameAR, -- Department/Clinic Arabic name.
                DepartmentNameEN, -- Department/Clinic English name.
                ConsultationPrice, 
                NextAvailableDate, 
                DoctorImageUrl, 
                DescriptionAR,
                DescriptionEN
                FROM your_table WHERE your_conditions;
Stored procedure: dbo.blsmy_proc_createAppointment
SP Parameter Type Remarks
@SlotID nvarchar(50) The unique Id of the Slot/Appointment of the doctor. If your system does not support Slots; just ignore it's value
@FromTime nvarchar(50) The start time of the appointment. Format: hh:mm tt i.e 09:00 PM
@ToTime nvarchar(50) The end time of the appointment. Format: hh:mm tt i.e 10:00 PM
@Date nvarchar(50) The date of the appointment. Format: yyyy-MM-dd
@DoctorID nvarchar(50)
@ParentNationalID nvarchar(50) The main account holder's national ID
@ParentMobile nvarchar(50) The main account holder's mobile number
@ParentFName nvarchar(50) The main account holder's first name
@ParentLName nvarchar(50) The main account holder's last name
@ParentDOB nvarchar(50) The main account holder's date of birth. Format: yyyy-MM-dd
@ParentSex nvarchar(1) The main account holder's sex. Format: M or F
@ParentNationality nvarchar(2) The main account holder's nationality. Format: ISO 3166-1 alpha-2 code
@PatientNationalID nvarchar(50) The subsidiary's (Son, daughter, wife, etc.) national ID
@PatientFName nvarchar(50) The subsidiary's first name
@PatientLName nvarchar(50) The subsidiary's last name
@PatientLDOB nvarchar(50) The subsidiary's date of birth. Format: yyyy-MM-dd
@PatientSex nvarchar(1) The subsidiary's sex. Format: M or F
@PatientNationality nvarchar(2) The subsidiary's nationality. Format: ISO 3166-1 alpha-2 code 
CREATE PROCEDURE [dbo].[blsmy_proc_createAppointment]-- Name of the stored procedure.
@SlotID nvarchar(50),
@FromTime nvarchar(50),
@ToTime nvarchar(50),
@Date nvarchar(50),
@DoctorID nvarchar(50),
@ParentNationalID nvarchar(50),
@ParentMobile nvarchar(50),
@ParentFName nvarchar(50),
@ParentLName nvarchar(50),
@ParentDOB nvarchar(50),
@ParentSex nvarchar(1),
@ParentNationality nvarchar(2),
@PatientNationalID nvarchar(50),
@PatientFName nvarchar(50),
@PatientLName nvarchar(50),
@PatientDOB nvarchar(50),
@PatientSex nvarchar(1),
@PatientNationality nvarchar(2)

as
-- Your business logic to create an appointment goes here. For example, you might:
                   * Check if the parent exists, create a new record for the parent if he does not exist.
                   * Create a new record for the Subsidiary under his parent account to logically connect them together.
                   * Update the Parent info or the subsidiary info as per the @NationalID parameter value.
                   * Insert a new record into a Appointments/bookings table and then return the newly created Id of the transaction.

-- Very important: Make sure to handle any potential errors and edge cases in your actual implementation.
	-- Sample of returning an error message due to a wrong business logic:
    IF(any_error_logic)
	BEGIN
            SELECT 
                'Any-Code' AS ErrorCode,
                'Any-Message' AS ErrorMessageAR,
                'Any-Message' AS ErrorMessageEN;
               -- The error message text will be displayed directly in the mobile application to the patient, 
                    so make sure to provide clear and user-friendly messages in both Arabic and English.
        RETURN;
	END

  -- Finally, return columns expected by the API mapper (Make sure the use the AS keyword for aliases).
  -- Make sure all returned columns are of type nvarchar.
  -- Sample of a SELECT statement that returns the required columns:
SELECT AppointmentId FROM your_table
Stored procedure: dbo.blsmy_proc_getSlotsByDoctorIDAndDate
SP Parameter Type Remarks
@DoctorID nvarchar(50)
@Date nvarchar(50) yyyy-MM-dd 
CREATE PROC [dbo].[blsmy_proc_getSlotsByDoctorIDAndDate]
@DoctorID nvarchar(50),
@Date nvarchar(50)
AS
BEGIN
-- Your business logic to return active departments goes here.
-- Very important: Make sure to handle any potential errors and edge cases in your actual implementation.
Sample of returning an error message due to a wrong business logic:
	IF(any_error_logic)
	BEGIN
            SELECT 
                'Any-Code' AS ErrorCode,
                'Any-Message' AS ErrorMessageAR,
                'Any-Message' AS ErrorMessageEN;
               -- The error message text will be displayed directly in the mobile application to the patient, 
                    so make sure to provide clear and user-friendly messages in both Arabic and English.
        RETURN;
	END

  -- Finally, return columns expected by the API mapper (Make sure the use the AS keyword for aliases).
  -- Make sure all returned columns are of type nvarchar.
  -- Sample of a SELECT statement that returns the required columns:
                    SELECT 
                    SlotId,   -- The unique Id of the Slot/Appointment of the doctor. If your system does not support Slots; use '-1'
                    DoctorId, 
                    Date,   -- yyyy-MM-dd
                    FromTime,   --Sample: 09:00 PM
                    ToTime,   --Sample: 10:00 PM
                    SessionDuration,   -- In minutes 
                    Status   -- 'Booked' or 'avalible' 
                    FROM your_table
END
Stored procedure: dbo.blsmy_proc_getAppointments
This procedure should return all the appointments created by Blsmy application in your database during a period. We use this procedure to update our system with the latest status of the appointments (Cancelled, Completed, NoShow, etc.).
Additionaly, if you have our white-label application, this procedure can help the patients to see their appointments history in the app by fetching the old appointments from your database even if these appointments/bookings were not created by our application . In this case, -If you wish- you should change the implementation of this procedure to return all the appointments of the patient (not only the appointments created by our app).
SP Parameter Type Remarks
@FromDate nvarchar(50) yyyy-MM-dd HH:mm:ss i.e. 2025-01-13 20:15:30
@ToDate nvarchar(50) yyyy-MM-dd HH:mm:ss i.e. 2025-01-14 20:15:30
@AppointmentId nvarchar(50) If provided, the procedure should return a single appointment matching this ID. 
CREATE PROCEDURE [dbo].[blsmy_proc_getAppointments]-- Name of the stored procedure.
@FromDate nvarchar(50),
@ToDate nvarchar(50),
@appointmentId nvarchar(50) = null  --Optional parameter to filter the appointments by a specific appointment id.
    AS
                -- Your business logic to retrieve appointments goes here.

-- Very important: Make sure to handle any potential errors and edge cases in your actual implementation.
Sample of returning an error message due to a wrong business logic:
	IF(any_error_logic)
	BEGIN
            SELECT 
                'Any-Code' AS ErrorCode,
                'Any-Message' AS ErrorMessageAR,
                'Any-Message' AS ErrorMessageEN;
               -- The error message text will be displayed directly in the mobile application to the patient, 
                    so make sure to provide clear and user-friendly messages in both Arabic and English.
        RETURN;
	END

  -- Finally, return columns expected by the API mapper (Make sure the use the AS keyword for aliases).
  -- Make sure all returned columns are of type nvarchar.
  -- Sample of a SELECT statement that returns the required columns:
                    SELECT 
                    AppointmentId,   -- The unique Id of the Appointment.
                    DoctorId, 
                    DoctorNameAR,
                    DoctorNameEN,
                    DepartmentNameAR,   -- Department/Clinic Name in Arabic.
                    DepartmentNameEN,   -- Department/Clinic Name in English.
                    Date,   -- yyyy-MM-dd
                    FromTime,   --Sample: 09:00 PM
                    ToTime,   --Sample: 10:00 PM
                    PatientNationalID,
                    PatientName,
                    AppointmentStatus,   -- The status of the appointment.
                    VisitID, -- The VisitID tied to the appointment if applicable. 
                                                            This will be used to get the prescriptions, lab results, and other related 
                                                            information for the visit.
                                                            It is very important to return the correct value here that should be used to 
                                                            get the related information of the visit.
                    ArrivalStatus,  -- This what determins if the patient has arrived or not.
                                        * For success or completed appointments, use the string 'Arrived'
                                        * For any other statuses, return the status string as in your system.
                    
                    FollowUpVisitID,   -- The ID of the follow-up visit if applicable.
                    FROM your_table;
Stored procedure: dbo.blsmy_proc_getPrescriptionsByVisitIDOrAppointmentID
SP Parameter Type Remarks
@AppointmentID nvarchar(50) The unique Id of the Appointment.
@VisitID nvarchar(50) The unique Id of the Visit.This may be the same as the AppointmentID if your system does not support VisitIDs. We should have got it's value in a previous call to Get Appointments.
@NationalID nvarchar(50) The NationalID of the Patient. You can use it to double check if the requested presecriptions are realted to this patient
@MobileNo nvarchar(50) The mobile number of the account owner. You can use it to double check if the requested presecriptions are realted to this patient 
CREATE PROCEDURE [dbo].[blsmy_proc_getPrescriptionsByVisitIDOrAppointmentID]-- Name of the stored procedure.
@AppointmentID nvarchar(50),
@VisitID nvarchar(50),
@NationalID nvarchar(50),
@MobileNo nvarchar(50)
as
-- Your business logic to retrieve prescriptions goes here.

-- Very important: Make sure to handle any potential errors and edge cases in your actual implementation.
Sample of returning an error message due to a wrong business logic:
	IF(any_error_logic)
	BEGIN
            SELECT 
                'Any-Code' AS ErrorCode,
                'Any-Message' AS ErrorMessageAR,
                'Any-Message' AS ErrorMessageEN;
               -- The error message text will be displayed directly in the mobile application to the patient, 
                    so make sure to provide clear and user-friendly messages in both Arabic and English.
        RETURN;
	END

  -- Finally, return columns expected by the API mapper (Make sure the use the AS keyword for aliases).
  -- Make sure all returned columns are of type nvarchar.
  -- Sample of a SELECT statement that returns the required columns:
                     SELECT PrescriptionID,
                            PrescriptionDate, 
                            DoctorID, 
                            DoctorNameAR, 
                            DoctorNameEN, 
                            DepartmentNameAR,
                            DepartmentNameEN,
                            NationalID,  -- The NationalID of the patient.
                            ScientificCodes,  -- The scientific codes related to the prescribed item.
                            ScientificCodesName, 
                            Dosage, 
                            Frequency, 
                            NumOfDays, 
                            VisitID, 
                            VisitDate, 
                            ItemID, -- The unique Id of the prescribed item. It can be a medicine, a lab test, or any other service.
                            ProductName,  -- The Name of the prescribed item. For medicines, it should be the medicine name. 
                                          For lab tests, it should be the test name.
                            Strength  -- The strength of the prescribed item. For medicines, it can be 250 mg, 500 mg, etc. 
                                      For lab tests, it can be null or empty.
FROM your_table
Stored procedure: dbo.blsmy_proc_getPatientApprovals
SP Parameter Type Remarks
@AppointmentID nvarchar(50)
@VisitID nvarchar(50)
@NationalID nvarchar(50) The NationalID of the Patient.
@MobileNo nvarchar(50) The mobile number of the account owner. 
CREATE PROCEDURE [dbo].[blsmy_proc_getPatientApprovals]-- Name of the stored procedure.
@AppointmentID nvarchar(50),
@VisitID nvarchar(50),
@NationalID nvarchar(50),
@MobileNo nvarchar(50)
AS
-- Your business logic to retrieve approvals for a patient goes here.

-- Very important: Make sure to handle any potential errors and edge cases in your actual implementation.
Sample of returning an error message due to a wrong business logic:
	IF(any_error_logic)
	BEGIN
            SELECT 
                'Any-Code' AS ErrorCode,
                'Any-Message' AS ErrorMessageAR,
                'Any-Message' AS ErrorMessageEN;
               -- The error message text will be displayed directly in the mobile application to the patient, 
                    so make sure to provide clear and user-friendly messages in both Arabic and English.
        RETURN;
	END

  -- Finally, return columns expected by the API mapper (Make sure the use the AS keyword for aliases).
  -- Make sure all returned columns are of type nvarchar.
  -- Sample of a SELECT statement that returns the required columns:
                    SELECT 
                            VisitID, 
                            VisitDate, 
                            Doctor, 
                            DoctorNameAR, 
                            DoctorNameEN, 
                            DepartmentNameAR, 
                            DepartmentNameEN, 
                            ServiceApprovalDate, 
                            ServiceApprovalID, 
                            ServiceID,  
                            ServiceNameAR, 
                            ServiceNameEN, 
                            UnitsRequested, 
                            ApprovalStatusCode, 
                            ApprovalStatusEN, --The Status of the approval request
                                             * In case of success use 'Approved'
                                             * In case of Rejection use 'Rejected'
                                             * For any other cases, use the Status Name in your system.
                            ApprovalStatusAR,                                             
                            Notes  --Reason of the rejection if applicable. 
    FROM your_table
Stored procedure: dbo.blsmy_proc_getPatientLabTests
SP Parameter Type Remarks
@AppointmentID nvarchar(50) The Id of the appointment for filtering the lab tests.
@VisitID nvarchar(50) The Id of the visit for filtering the lab tests.
@NationalID nvarchar(50) The National ID of the patient for filtering the lab tests.
@MobileNo nvarchar(200) The mobile number of the main account holder.
You can use it to double check if the NationalID is associated with this mobile number either as a main account or a subsidiary.		 
CREATE PROCEDURE [dbo].[blsmy_proc_getPatientLabTests]-- Name of the stored procedure.
@AppointmentID nvarchar(50),
@VisitID nvarchar(50),
@NationalID nvarchar(50),
@MobileNo nvarchar(50)
as
-- Your business logic to retrieve patient lab test results goes here.


-- Very important: Make sure to handle any potential errors and edge cases in your actual implementation.
Sample of returning an error message due to a wrong business logic:
	IF(any_error_logic)
	BEGIN
            SELECT 
                'Any-Code' AS ErrorCode,
                'Any-Message' AS ErrorMessageAR,
                'Any-Message' AS ErrorMessageEN;
               -- The error message text will be displayed directly in the mobile application to the patient, 
                    so make sure to provide clear and user-friendly messages in both Arabic and English.
        RETURN;
	END

  -- Finally, return columns expected by the API mapper (Make sure the use the AS keyword for aliases).
  -- Make sure all returned columns are of type nvarchar.
  -- Sample of a SELECT statement that returns the required columns:
                SELECT 
                        VisitID,   -- The ID of the visit or the appointment.
                        VisitDate, 
                        ResultDateTime,   -- The date and time when the test result was available.
                        DoctorID, 
                        DoctorNameAR, 
                        DoctorNameEN, 
                        DepartmentNameAR, 
                        DepartmentNameEN, 
                        NationalID,   -- The National ID of the patient Who made the test.
                        TestID,   -- The unique Id of the lab test in your system.
                        TestNameAR,   -- The name of the test in Arabic.
                        TestNameEN,   -- The name of the test in English.
                        ParamName,   -- The name of the test parameter if applicable (e.g., Hemoglobin, WBC, etc.)
                        Result,   -- The result value of the test parameter or of the test if no parameters exist.
                        ParamNormalRange,   -- The normal range for the test parameter if applicable.
                        ParamHigh, 
                        ParamLow, 
                        RefRange,   -- The reference range for the test parameter if applicable.
                        ParamUnit, 
                        Notes 
                  FROM your_table
Stored procedure: dbo.blsmy_proc_confirmOrCancelAppointment
SP Parameter Type Remarks
@AppointmentID nvarchar(50)
@AppointmentNewStatus nvarchar(10) 'CONFIRMED' OR 'CANCELLED' 
CREATE PROCEDURE [dbo].[blsmy_proc_confirmOrCancelAppointment]-- Name of the stored procedure.
@AppointmentID nvarchar(50),
@AppointmentNewStatus nvarchar(10)
as
-- Your business logic to confirm or cancel an appointment goes here.
-- Very important: Make sure to handle any potential errors and edge cases in your actual implementation.
Sample of returning an error message due to a wrong business logic:
	IF(any_error_logic)
	BEGIN
            SELECT 
                'Any-Code' AS ErrorCode,
                'Any-Message' AS ErrorMessageAR,
                'Any-Message' AS ErrorMessageEN;
               -- The error message text will be displayed directly in the mobile application to the patient, 
                    so make sure to provide clear and user-friendly messages in both Arabic and English.
        RETURN;
	END

  -- Finally, return columns expected by the API mapper (Make sure the use the AS keyword for aliases).
  -- Make sure all returned columns are of type nvarchar.
  -- Sample of a SELECT statement that returns the required columns:
SELECT 
        'True' as Success -- As long as you have passed the above check and reached this line of code, return 'True'. 
                    For failure cases, you should have returned that in the previous error-logic section.
  FROM your_table