CODEWITHSUNDEEP
sql-serverstored-procedures
Radomir
Radomir

Reputation: 9

SQL Server : parameterized stored procedure

I have to do this in SQL Server. Assume that I have 2 tables.

Based on parameters Name and Surname, I have to take PhysicianID from Table1.

After that I have to create new record using insert into stored procedure.

Something like this

CREATE PROCEDURE FIND_PHYSICIANID
     @FirstName varchar(50),
     @LastName varchar(50)
AS
BEGIN
    DECLARE  @PhysicianID  int

    SELECT @PhysicianID = PhysicianID 
    FROM Table1
    WHERE FirstName = @FirstName  AND LastName = @LastName

    RETURN @PhysicianID
END

EXECUTE FIND_PHYSICIANID 'Kathlin','Jones' 

CREATE PROCEDURE ADD_APPOINTMENT     -- Create a new appointment    
    @AppointmentType VARCHAR(70), --Type of new appointment
    @pAppointmentDate DATE, -- Date of new appointment
    @aPhysicianID INT, --PhysicianID of requested physician (in this case during execution we will take value which we know-read from table for requested first and last name)     
    @apPatientID INT, --PatientID of chosen patient(let's say any from 1 to 14) 
    @aScheduleID INT, --ScheduleID, but here we have to take some ScheduleID for chosen PhysicianID (in this case during execution we will take value which we know-based on PHYSICIANID we may read value from table SCHEDULE)  
    @Status CHAR(1) -- Just Y or N
AS    -- This "AS" is required by the syntax of stored procedures. 
BEGIN   -- Insert the new appointment  
    INSERT INTO [APPOINTMENT]([AppointmentType], [AppointmentDate],[aPhysicianID],
                              [apPatientID], [aScheduleID], [Status-Canceled])   
    VALUES (@AppointmentType, @pAppointmentDate, @aPhysicianID, 
            @apPatientID, @aScheduleID, @Status); 
END; 

EXECUTE ADD_APPOINTMENT 'Vaccinations', '2017-0831', '@PhysicianID', '12', '289', 'N'

Upvotes: 1

Views: 388

Answers (2)

TOBrien
TOBrien

Reputation: 86

Presuming that the ability to find a physician is a common operation you could convert the FIND_PHYSICIANID stored procedure to a function and delay the lookup to within the consuming stored procedure that performs the operation.

create function [dbo].[FIND_PHYSICIANID]
(   
     @FirstName varchar(50),
     @LastName varchar(50)
)
returns int
as
begin

    declare @PhysicianId int

    select @PhysicianID = PhysicianID 
    from dbo.Table1
    where FirstName = @FirstName  
        and LastName = @LastName

    return @PhysicianId

end

This will still keep the logic of finding a physician centralised but allow you to perform other actions and possibly validation if the only information you have available to you is the full name. Yes, it is more parameters but this is assuming the required parameters for the stored procedures are a manageable amount.

create procedure [dbo].[ADD_APPOINTMENT]     -- Create a new appointment    
    @AppointmentType VARCHAR(70), --Type of new appointment
    @pAppointmentDate DATE, -- Date of new appointment
    @PhysicianFirstName varchar(50), -- // The first name of the physician
    @PhysicianLastName varchar(50),  -- // The last name of the physician   
    @apPatientID INT, --PatientID of chosen patient(let's say any from 1 to 14) 
    @aScheduleID INT, --ScheduleID, but here we have to take some ScheduleID for chosen PhysicianID (in this case during execution we will take value which we know-based on PHYSICIANID we may read value from table SCHEDULE)  
    @Status CHAR(1) -- Just Y or N
AS    -- This "AS" is required by the syntax of stored procedures. 
BEGIN   -- Insert the new appointment  

    declare @aPhysicianID int
    select @aPhysicianID = [dbo].[FIND_PHYSICIANID](@PhysicianFirstName, @PhysicianLastName varchar(50))

    INSERT INTO [APPOINTMENT]([AppointmentType], [AppointmentDate],[aPhysicianID],
                              [apPatientID], [aScheduleID], [Status-Canceled])   
    VALUES (@AppointmentType, @pAppointmentDate, @aPhysicianID, 
            @apPatientID, @aScheduleID, @Status); 
END

Alternatively, if it is desired to be separated and keep the existing stored procedure parameter signature then the previous answer that has the caller lookup the physician id via the stored procedure locally and then pass that parameter into the add appointment stored procedure should suffice your requirements.

As per the below pseudocode

  1. Declare a variable for @physicianid of type int
  2. Assign the @physicianid variable to the output of the FIND_PHYSICIANID stored procedure
  3. Execute ADD_APPOINTMENT stored procedure with @physicianid variable as an input

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13403

You can get return id like this.

DECLARE  @PhysicianID  int
EXECUTE @PhysicianID  = FIND_PHYSICIANID 'Kathlin','Jones'

you can use this param like this

EXECUTE ADD_APPOINTMENT 'Vaccinations','2017-0831', @PhysicianID, '12','289','N'

Upvotes: 1

Related Questions