February 6, 2018 at 8:45 am
Hi,
I have a procedure that does MERGE. however I have new changes, please see below, these are some of the variables I have:
CREATE PROCEDURE procedure_name
@procedureNameID OUT
@MemberID
@StudentID
@errorcode OUT
@ErrorDesc OUT
AS
BEGIN
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
END
I am getting Member and Student ID as parameters, but I want to Test them first to see if they exist in my tables, if they don't, I want the procedure and I want it return a message that Member or Student ID does not exist.
I have to test MemerID alone first;
IF( SELECT MemberID
FROM database.dbo.view_MyView
WHERE MemberID = @MemberID ) IS NULL
If MemberID is null, I have to throw an error and return the @MemberID with a message back to the users that the @MemberID they sent doesn't exist, the same with StudentID.
I've been trying RAISERROR but I don't been to get it right, instead it runs the whole proc. please help.
February 6, 2018 at 9:03 am
Try something like this
BEGIN TRY
IF NOT EXISTS (
SELECT 1
FROM dbo.view_MyView
WHERE MemberID = @MemberID
)
BEGIN
THROW 50000
,'MemberId does not exist'
,1;
END
END TRY
BEGIN CATCH
THROW
END CATCH
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 6, 2018 at 9:15 am
What severity are you using in your RAISERROR?
It sounds like you don't have your BEGIN/END statements placed properly. You should never be able to process both sides of an IF...THEN...ELSE statement.
Also, I would use IF NOT EXISTS ( <subquery> ) rather than IF ( <subquery> ) IS NULL, for several reasons.
1) It's more widely used.
2) It's more accurate. It can distinguish between failing to return a record and returning a record whose column contains a NULL value.
3) It doesn't fail if the subquery returns multiple records
4) It doesn't fail if the subquery returns multiple fields.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 6, 2018 at 10:05 pm
Phil Parkin - Tuesday, February 6, 2018 9:03 AMTry something like this
BEGIN TRY
IF NOT EXISTS (
SELECT 1
FROM dbo.view_MyView
WHERE MemberID = @MemberID
)
BEGIN
THROW 50000
,'MemberId does not exist'
,1;
END
END TRYBEGIN CATCH
THROW
END CATCH
Thank you Phil. How do I then return that "MemberID does not exist" back to users who calls my proc?
February 7, 2018 at 5:06 am
hoseam - Tuesday, February 6, 2018 10:05 PMThank you Phil. How do I then return that "MemberID does not exist" back to users who calls my proc?
How are they calling the proc? Web form? Windows app? Something else?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply