How to Perform Multiple Task in T-SQL

Abraham Olatubosun

9y892

Dear Code Masters,

Hope this mail meet you all in good health. Please i will aprecaite any workable solution to my question.

I want to create a single store procedure that i want to INSERT to a table based on some crateria, after then use this table new record to update itself based on other crateria

E.g :

use IQCARE
INSERT INTO tbl_ARTSTARTDATE_TABLE (ptn_pk,PatientID,HospitalNumber,Sex,AgeAtStartART,DOB,ARTStartDate,ArtStatus )
(select ptn_pk,PatientID,HospitalNumber,Sex,AgeAtStartART,DOB,ARTStartDate,ArtStatus from tbl_RADET_TABLE)

the above code works, i want to use the PTN_PK to update tbl_ARTSTARTDATE_TABE based on the crateria specied as shown below :

UPDATE tbl_ARTSTARTDATE_TABLE a SET LastPickupDate =(Select top 1 DispensedByDate from VW_PatientPharmacy b Where b.Ptn_pk =a.Ptn_pk
and DispensedByDate is not null order by DispensedByDate desc)

I want all the commands to be in one Stored Procedure.

Thank you all

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gokhul Varman9y ago
You can refer the link for more explanation:https://stackoverflow.com/questions/3847336/how-to-perform-multiple-sql-tasks-when-using-sql-within-code-in-this-case-vbscr
jude somtoo9y ago
create procedure sp_acceptance_insert
(
@studentid int output,
@imageurl nvarchar(250),
@fullname nvarchar(10),
@email nvarchar(100),
@gender nchar(10),
@Department nvarchar(50),
@faculty nvarchar(50),
@regno int,
@address nvarchar(250),
@modeofstudy nvarchar(50),
@dateofadmission nchar(50),
@programmeofstudy nvarchar(50)
)
as
insert into acceptance(imageurl,fullname,email,gender,Department,faculty,regno,[address],modeofstudy,dateofadmission,programmeofstudy)
values(@imageurl,@fullname,@email,@gender,@Department,@faculty,@regno,@address,@modeofstudy,@dateofadmission,@programmeofstudy)
set @studentid = SCOPE_IDENTITY();

create procedure sp_acceptance_update
(
@studentid int output,
@imageurl nvarchar(250),
@fullname nvarchar(10),
@email nvarchar(100),
@gender nchar(10),
@Department nvarchar(50),
@faculty nvarchar(50),
@regno int,
@address nvarchar(250),
@modeofstudy nvarchar(50),
@dateofadmission nchar(50),
@programmeofstudy nvarchar(50)
)
as
update acceptance set imageurl = @imageurl, fullname = @fullname, email = @email, gender = @gender,dateofadmission = @dateofadmission,
Department = @Department, faculty = @faculty, regno = @regno, [address] = @address,modeofstudy = @modeofstudy,programmeofstudy=@programmeofstudy
where studentid = @studentid;

i hope this helps