mercredi 5 décembre 2018

IF statement using output of SPROC SQL

I am creating stored procedures (sprocs) to perform operations on my tables in my database. I have a sproc SubjectExists that returns '1' if the subject name entered is in the subject table and returns '0' if it does not exist.

CREATE PROCEDURE SubjectExists @SubjName varhcar(20)
AS
SELECT CASE WHEN EXISTS(
SELECT *
FROM Subject 
WHERE Subject_Name = @SubjName
)
THEN CAST (1 AS BIT)
ELSE CAST (0 AS BIT)
END

I am now making another sproc that deletes a subject from the table. I want to make this sproc such that it uses SubjectExists and if the output of it is a 1 (i.e. the subject does exist) then it deletes the subject and if the output from SubjectExists is 0, it does nothing.

How would I go about doing this?

I have tried experimenting with the below but no luck so far.

CREATE PROCEDURE DeleteSubject @SubjName varchar(20)
AS
IF (EXEC StudentExists Bob)
DELETE FROM Subject 
WHERE Subject_Name = @SubjName;

Can anyone please guide me as to how I would do this. Thanks

Aucun commentaire:

Enregistrer un commentaire