February 19, 2018 at 3:29 am
Hi,
I have this proc
CREATE PROC proc_name
@mytableID INT OUT
,@MemberID INT
,@BenefitID INT
,@MemberName VARCHAR(100)
,@Status VARCHR(20)
,@ErrorCode INT OUT
,@ErrorDesc VARCHAR(250) OUT
AS
BEGIN
BEGIN TRY
DECLARE
@LogErrorCode INT
,@ErrorStep VARCHAR(250)
IF @MemerID IS NULL
BEGIN
SET @ErrorDesc = 'Invalid Member'
END
IF EXIST(SELECT 1 FROM myTable WHERE MemberID = @MemerID AND BenefitID = @BenefitID)
BEGIN
SET @ErrorDesc = 'Record exist already'
END
INSERT INTO mytable(
MemberID
,BenefitID
,Name
,Status
)
VALUES(
@MemberID
,@BenefitID
,@Name
,@Status
)
SELECT
@mytableID = SCOPE_IDENTITY()
,@ErrorCode = -1
,@ErrorDesc = 'Success'
END TRY
BEGIN CATCH
IF @ErrorDescription IS NOT NULL
BEGIN
SELECT @ErrorCode = -999
,@ErrorDescription = @ErrorDescription
,@Success = 0
END
ELSE
BEGIN
SELECT @LogErrorCode = ERROR_NUMBER()
,@ErrorStep = ERROR_MESSAGE()
,@ErrorCode = @LogErrorCode
,@ErrorDescription = @ErrorStep
,@Success = 0
END
END CATCH
END
When I execute this proc and pass NULL for MemberID, I do get 'Invalid Member' custom error, from the CATCH, however when MemberID and BenefitID are valid, and the records already exist in the table, I'm not getting 'Record exist already'.
Please help.
February 19, 2018 at 3:36 am
There's no logic to cause that error, that's why. you're setting the value of the error message, but you're not raising one; and your INSERT statement (as far as we can tell) won't generate one. This is much like setting to value to a variable, then never referencing that variable again and wondering why it's not output in your SELECT statement.
Instead of using SET, have a look at RAISERROR (Transact-SQL).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 19, 2018 at 3:44 am
Thom A - Monday, February 19, 2018 3:36 AMThere's no logic to cause that error, that's why. you're setting the value of the error message, but you're not raising one; and your INSERT statement (as far as we can tell) won't generate one. This is much like setting to value to a variable, then never referencing that variable again and wondering why it's not output in your SELECT statement.Instead of using SET, have a look at RAISERROR (Transact-SQL).
Thank you, got it right.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply