SQL query return error as select statment if fails

I would like to catch the error and return the error through a select statement on this script , can you please assist , I have provided the code snipped below ,  this proc will be working in a spreadsheet and the table does not allow nulls , i want it to return an error when they try to insert nulls on operation 'A'


ALTER PROC  [dbo].[sp_cmk_Lawson_PR_Canada_Stat_psg_Updatedata_XL]
						
						@FC CHAR(1),
						@PAY_SUM_GROUP CHAR (5),
						--@DESCRIPTION CHAR(80) , 
						--@COMPANY CHAR (4),
						@Elig_StatHol_Pay_AB CHAR(9) ,
						@Elig_StatHol_Pay_BC CHAR(9) ,
						@Elig_StatHol_Pay_MB CHAR (9),
						@Elig_StatHol_Pay_NB CHAR (9),
						@Elig_StatHol_Pay_NL CHAR (9), 
						@Elig_StatHol_Pay_NF CHAR (9),
						@Elig_StatHol_Pay_NN CHAR (9),
						@Elig_StatHol_Pay_NS CHAR (9),
						@Elig_StatHol_Pay_NT CHAR (9),
						@Elig_StatHol_Pay_ON CHAR (9),
						@Elig_StatHol_Pay_PE CHAR (9),
						@Elig_StatHol_Pay_QC CHAR (9),
						@Elig_StatHol_Pay_SK CHAR (9),
						@Elig_StatHol_Pay_YK CHAR (9),
						@Elig_StatHol_Pay_Calc_AB CHAR(9) ,
						@Elig_StatHol_Pay_Calc_BC CHAR(9) ,
						@Elig_StatHol_Pay_Calc_MB CHAR (9),
						@Elig_StatHol_Pay_Calc_NB CHAR (9),
						@Elig_StatHol_Pay_Calc_NL CHAR (9), 
						@Elig_StatHol_Pay_Calc_NF CHAR (9),
						@Elig_StatHol_Pay_Calc_NN CHAR (9),
						@Elig_StatHol_Pay_Calc_NS CHAR (9),
						@Elig_StatHol_Pay_Calc_NT CHAR (9),
						@Elig_StatHol_Pay_Calc_ON CHAR (9),
						@Elig_StatHol_Pay_Calc_PE CHAR (9),
						@Elig_StatHol_Pay_Calc_QC CHAR (9),
						@Elig_StatHol_Pay_Calc_SK CHAR (9),
						@Elig_StatHol_Pay_Calc_YK CHAR (9)
			
	AS 
	
	BEGIN TRY  
 BEGIN TRANSACTION 
BEGIN
      
      SET NOCOUNT ON;

	
	IF @FC in ( 'C' ,'c' )
				
BEGIN
		
	UPDATE cmkCanadaStatutoryHolidaysPaySumGrpInclude_New_TEST
		SET 

		 Elig_StatHol_Pay_AB = case when @Elig_StatHol_Pay_AB = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_AB = 'Include' then 1 end  ,
		 Elig_StatHol_Pay_BC = case when @Elig_StatHol_Pay_BC = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_BC = 'Include' then 1 end ,
		 Elig_StatHol_Pay_MB = case when @Elig_StatHol_Pay_MB= 'Exclude' then 0 
		 when @Elig_StatHol_Pay_MB= 'Include' then 1 end ,
		 Elig_StatHol_Pay_NB = case when @Elig_StatHol_Pay_NB = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_NB = 'Include' then 1 end ,
		 Elig_StatHol_Pay_NF = case when @Elig_StatHol_Pay_NF = 'Exclude' then 0   
		 when @Elig_StatHol_Pay_NF = 'Include' then 1 end ,
		 Elig_StatHol_Pay_NL = case when @Elig_StatHol_Pay_NL = 'Exclude' then 0  
		 when @Elig_StatHol_Pay_NL = 'Include' then 1 end ,
		 Elig_StatHol_Pay_NN = case when @Elig_StatHol_Pay_NN = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_NN = 'Include' then 1 end ,
		 Elig_StatHol_Pay_NS = case when @Elig_StatHol_Pay_NS = 'Exclude' then 0  
		 when @Elig_StatHol_Pay_NS = 'Include' then 1 end ,
		 Elig_StatHol_Pay_NT = case when @Elig_StatHol_Pay_NT = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_NT = 'Include' then 1 end ,
		 Elig_StatHol_Pay_ON = case when @Elig_StatHol_Pay_ON = 'Exclude' then 0  
		 when @Elig_StatHol_Pay_ON = 'Include' then 1 end ,
		 Elig_StatHol_Pay_PE = case when @Elig_StatHol_Pay_PE = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_PE = 'Include' then 1 end ,
		 Elig_StatHol_Pay_QC = case when @Elig_StatHol_Pay_QC = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_QC = 'Exclude' then 0 end ,
		 Elig_StatHol_Pay_SK = case when @Elig_StatHol_Pay_SK = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_SK = 'Exclude' then 0 end , 
		 Elig_StatHol_Pay_YK = case when @Elig_StatHol_Pay_YK = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_YK = 'Exclude' then 0 end ,
		 Elig_StatHol_Pay_Calc_AB = case when @Elig_StatHol_Pay_Calc_AB ='Exclude' then 0
		 when @Elig_StatHol_Pay_Calc_AB ='Include' then 1 end,
		 Elig_StatHol_Pay_Calc_BC =  case when @Elig_StatHol_Pay_Calc_BC = 'Exclude' then 0  
		 when @Elig_StatHol_Pay_Calc_BC = 'Include' then 1 end, 
		 Elig_StatHol_Pay_Calc_MB = case when @Elig_StatHol_Pay_Calc_MB = 'Exclude' then 0  
		 when @Elig_StatHol_Pay_Calc_MB = 'Include' then 1 end ,
		 Elig_StatHol_Pay_Calc_NB = case when @Elig_StatHol_Pay_Calc_NB = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_Calc_NB = 'Include' then 1 end,
		 Elig_StatHol_Pay_Calc_NF = case when @Elig_StatHol_Pay_Calc_NF = 'Exclude' then 0  
		 when @Elig_StatHol_Pay_Calc_NF = 'Exclude' then 0  end, 
		 Elig_StatHol_Pay_Calc_NL = case when @Elig_StatHol_Pay_Calc_NL = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_Calc_NL = 'Include' then 1 end, 
		 Elig_StatHol_Pay_Calc_NN = case when @Elig_StatHol_Pay_Calc_NN = 'Exclude' then 0  
		 when @Elig_StatHol_Pay_Calc_NN = 'Include' then 1 end, 
		 Elig_StatHol_Pay_Calc_NS = case when @Elig_StatHol_Pay_Calc_NS = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_Calc_NS = 'Include' then 1 end, 
		 Elig_StatHol_Pay_Calc_NT = case when @Elig_StatHol_Pay_Calc_NT = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_Calc_NT = 'Include' then 1 end,
		 Elig_StatHol_Pay_Calc_ON = case when @Elig_StatHol_Pay_Calc_ON = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_Calc_ON = 'Include' then 1 end,
		 Elig_StatHol_Pay_Calc_PE = case when @Elig_StatHol_Pay_Calc_PE = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_Calc_PE = 'Include' then 1 end,
		 Elig_StatHol_Pay_Calc_QC = case when @Elig_StatHol_Pay_Calc_QC = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_Calc_QC = 'Include' then 1 end,
		 Elig_StatHol_Pay_Calc_SK =  case when @Elig_StatHol_Pay_Calc_SK = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_Calc_SK = 'Include' then 1 end,
		 Elig_StatHol_Pay_Calc_YK = case when @Elig_StatHol_Pay_Calc_YK = 'Exclude' then 0 
		 when @Elig_StatHol_Pay_Calc_YK = 'Include' then 1 end

		WHERE Pay_Sum_Grp =  @PAY_SUM_GROUP -- AND COMPANY =@COMPANY
	 
		
DELETE  FROM cmkCanadaStatutoryHolidaysPaySumGrpInclude_New_TEST
		WHERE   Elig_StatHol_Pay_AB  IS NULL AND  Elig_StatHol_Pay_BC IS NULL AND  Elig_StatHol_Pay_MB IS NULL AND  Elig_StatHol_Pay_NB  IS NULL AND   Elig_StatHol_Pay_NF  IS NULL AND   Elig_StatHol_Pay_NL
		  IS NULL AND  Elig_StatHol_Pay_NN  IS NULL AND   Elig_StatHol_Pay_NS   IS NULL AND   Elig_StatHol_Pay_NT   IS NULL AND   Elig_StatHol_Pay_ON   IS NULL AND   Elig_StatHol_Pay_PE   IS NULL AND   Elig_StatHol_Pay_QC   IS NULL AND   Elig_StatHol_Pay_SK
		  IS NULL AND  Elig_StatHol_Pay_YK --CALC GROUP NOT INCLUDING HOURS 
		  IS NULL AND   Elig_StatHol_Pay_Calc_AB   IS NULL AND   Elig_StatHol_Pay_Calc_BC   IS NULL AND  Elig_StatHol_Pay_Calc_MB   IS NULL AND   Elig_StatHol_Pay_Calc_NB   IS NULL AND   Elig_StatHol_Pay_Calc_NF   IS NULL AND  Elig_StatHol_Pay_Calc_NL
		   IS NULL AND   Elig_StatHol_Pay_Calc_NN   IS NULL AND   Elig_StatHol_Pay_Calc_NS   IS NULL AND   Elig_StatHol_Pay_Calc_NT   IS NULL AND   Elig_StatHol_Pay_Calc_ON   IS NULL AND   Elig_StatHol_Pay_Calc_PE   IS NULL AND   Elig_StatHol_Pay_Calc_QC
		  IS NULL AND   Elig_StatHol_Pay_Calc_SK   IS NULL AND   Elig_StatHol_Pay_Calc_YK	IS NULL AND Elig_StatHol_Pay_Calc_PE IS NULL 

	SELECT 'UPDATE SUCCEEDED'
	END 

IF @FC IN ('A', 'a') 

	begin
 				
	INSERT INTO cmkCanadaStatutoryHolidaysPaySumGrpInclude_New_TEST
	(Pay_Sum_Grp, Elig_StatHol_Pay_AB,Elig_StatHol_Pay_BC,  Elig_StatHol_Pay_MB, Elig_StatHol_Pay_NB, Elig_StatHol_Pay_NF, Elig_StatHol_Pay_NL
		,Elig_StatHol_Pay_NN, Elig_StatHol_Pay_NS , Elig_StatHol_Pay_NT , Elig_StatHol_Pay_ON , Elig_StatHol_Pay_PE , Elig_StatHol_Pay_QC , Elig_StatHol_Pay_SK
		,Elig_StatHol_Pay_YK --CALC GROUP NOT INCLUDING HOURS 
		, Elig_StatHol_Pay_Calc_AB , Elig_StatHol_Pay_Calc_BC ,Elig_StatHol_Pay_Calc_MB , Elig_StatHol_Pay_Calc_NB , Elig_StatHol_Pay_Calc_NF ,Elig_StatHol_Pay_Calc_NL
		 , Elig_StatHol_Pay_Calc_NN , Elig_StatHol_Pay_Calc_NS , Elig_StatHol_Pay_Calc_NT , Elig_StatHol_Pay_Calc_ON , Elig_StatHol_Pay_Calc_PE , Elig_StatHol_Pay_Calc_QC
		, Elig_StatHol_Pay_Calc_SK , Elig_StatHol_Pay_Calc_YK )
		
		SELECT  @PAY_SUM_GROUP, 
		 CASE WHEN @Elig_StatHol_Pay_AB = 'Include' THEN 1  WHEN @Elig_StatHol_Pay_AB = 'Exclude' THEN 0 END			
     	,CASE WHEN @Elig_StatHol_Pay_BC = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_BC = 'Exclude' THEN 0 END 	
		,CASE WHEN @Elig_StatHol_Pay_MB = 'Include' THEN 1  WHEN @Elig_StatHol_Pay_MB = 'Exclude' THEN 0 END
		,CASE WHEN @Elig_StatHol_Pay_NB = 'Include' THEN 1  WHEN @Elig_StatHol_Pay_NB = 'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_NF = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_NF =  'Exclude' THEN 0 END 			  
		,CASE WHEN @Elig_StatHol_Pay_NL = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_NL =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_NN = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_NN =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_NS = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_NS =  'Exclude' THEN 0 END  
		,CASE WHEN @Elig_StatHol_Pay_NT = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_NT =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_ON = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_ON =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_PE = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_PE =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_QC = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_QC =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_SK = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_SK =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_YK = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_YK =  'Exclude' THEN 0 END   --CALC GROUP NOT INCLUDING HOURS 
		,CASE WHEN @Elig_StatHol_Pay_Calc_AB = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_AB =  'Exclude' THEN 0 END    
		,CASE WHEN @Elig_StatHol_Pay_Calc_BC = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_BC =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_Calc_MB = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_MB =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_Calc_NB = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_NB =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_Calc_NF = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_NF =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_Calc_NL = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_NL =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_Calc_NN = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_NN =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_Calc_NS = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_NS =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_Calc_NT = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_NT =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_Calc_ON = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_ON =  'Exclude' THEN 0 END  
		,CASE WHEN @Elig_StatHol_Pay_Calc_PE = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_PE =  'Exclude' THEN 0 END  
		,CASE WHEN @Elig_StatHol_Pay_Calc_QC = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_QC =  'Exclude' THEN 0 END 
		,CASE WHEN @Elig_StatHol_Pay_Calc_SK = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_SK =  'Exclude' THEN 0 END  
		,CASE WHEN @Elig_StatHol_Pay_Calc_YK = 'Include' THEN 1 WHEN @Elig_StatHol_Pay_Calc_YK =  'Exclude' THEN 0 END    
	
-- REMOVE BLANK ENTRIES FROM SPR		
 DELETE  FROM CMK_HRACCT..cmkCanadaStatutoryHolidaysPaySumGrpInclude_New_TEST
WHERE   Elig_StatHol_Pay_AB  IS NULL AND  Elig_StatHol_Pay_BC IS NULL AND  Elig_StatHol_Pay_MB IS NULL AND  Elig_StatHol_Pay_NB  IS NULL AND   Elig_StatHol_Pay_NF  IS NULL AND   Elig_StatHol_Pay_NL
		  IS NULL AND  Elig_StatHol_Pay_NN  IS NULL AND   Elig_StatHol_Pay_NS   IS NULL AND   Elig_StatHol_Pay_NT   IS NULL AND   Elig_StatHol_Pay_ON   IS NULL AND   Elig_StatHol_Pay_PE   IS NULL AND   Elig_StatHol_Pay_QC   IS NULL AND   Elig_StatHol_Pay_SK
		  IS NULL AND  Elig_StatHol_Pay_YK --CALC GROUP NOT INCLUDING HOURS 
		  IS NULL AND   Elig_StatHol_Pay_Calc_AB   IS NULL AND   Elig_StatHol_Pay_Calc_BC   IS NULL AND  Elig_StatHol_Pay_Calc_MB   IS NULL AND   Elig_StatHol_Pay_Calc_NB   IS NULL AND   Elig_StatHol_Pay_Calc_NF   IS NULL AND  Elig_StatHol_Pay_Calc_NL
		   IS NULL AND   Elig_StatHol_Pay_Calc_NN   IS NULL AND   Elig_StatHol_Pay_Calc_NS   IS NULL AND   Elig_StatHol_Pay_Calc_NT   IS NULL AND   Elig_StatHol_Pay_Calc_ON   IS NULL AND   Elig_StatHol_Pay_Calc_PE   IS NULL AND   Elig_StatHol_Pay_Calc_QC
		  IS NULL AND   Elig_StatHol_Pay_Calc_SK   IS NULL AND   Elig_StatHol_Pay_Calc_YK	IS NULL
		
;WITH DP_CHECKER AS ( --will check duplicates and delete if user keeps adding
			SELECT PAY_SUM_GRP , RN = ROW_NUMBER()OVER(PARTITION BY PAY_SUM_GRP ORDER BY PAY_SUM_GRP) 
			
			FROM cmkCanadaStatutoryHolidaysPaySumGrpInclude_New_TEST)
			
			DELETE FROM DP_CHECKER WHERE RN > 1

	SELECT 'ADD SUCCEEDED' 
end

	COMMIT TRANSACTION  
END
END TRY  
BEGIN CATCH  
  IF @@TRANCOUNT > 0   
   BEGIN  
    ROLLBACK TRANSACTION  
   END  
  
  --Raise Error  
  DECLARE @ErrMsg varchar(4000), @ErrSeverity int  
  SELECT   @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()  
  RAISERROR (@ErrMsg, @ErrSeverity, 1)  
END CATCH
SELECT   @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()

Open in new window

valentinemhlangaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ste5anConnect With a Mentor Senior DeveloperCommented:
I would split the function into two: an canonical, which works with the native data types.

ALTER PROC [dbo].[p_cmk_Lawson_PR_Canada_Stat_psg_Updatedata_XL_Internal]
    @FC CHAR(1) ,
    @PAY_SUM_GROUP CHAR(5) ,
    @Elig_StatHol_Pay_AB BIT ,
    @Elig_StatHol_Pay_Calc_AB BIT
AS
    SET NOCOUNT ON;
	
    DECLARE @NO_ERROR INT; SET @NO_ERROR = 0;
    DECLARE @ARGUMENT_ERROR INT; SET @ARGUMENT_ERROR = 1;
    DECLARE @PRIMARY_KEY_ERROR INT; SET @PRIMARY_KEY_ERROR = 2;

    DECLARE @Result INT;
    SET @Result = @NO_ERROR;

    IF ( @PAY_SUM_GROUP IS NULL
         OR @Elig_StatHol_Pay_AB IS NULL
         OR @Elig_StatHol_Pay_Calc_AB IS NULL
       )
        RETURN @ARGUMENT_ERROR; -- One or more parameter are NULL.

    IF @FC IN ( 'C', 'c' )
        BEGIN		
            UPDATE  cmkCanadaStatutoryHolidaysPaySumGrpInclude_New_TEST
            SET     Elig_StatHol_Pay_AB = @Elig_StatHol_Pay_AB ,
                    Elig_StatHol_Pay_Calc_AB = @Elig_StatHol_Pay_Calc_AB
            WHERE   Pay_Sum_Grp = @PAY_SUM_GROUP;	 		

            IF ( @@ROWCOUNT = 0 )
                SET @Result = @PRIMARY_KEY_ERROR; -- PK does not exist, no update took place.
        END;

    IF @FC IN ( 'A', 'a' )
        BEGIN 				
            IF EXISTS ( SELECT  *
                        FROM    cmkCanadaStatutoryHolidaysPaySumGrpInclude_New_TEST
                        WHERE   Pay_Sum_Grp = @PAY_SUM_GROUP )
                BEGIN 
                    INSERT  INTO cmkCanadaStatutoryHolidaysPaySumGrpInclude_New_TEST
                            ( Pay_Sum_Grp ,
                              Elig_StatHol_Pay_AB ,
                              Elig_StatHol_Pay_Calc_AB 
                      
                            )
                            SELECT  @PAY_SUM_GROUP ,
                                    @Elig_StatHol_Pay_AB ,
                                    @Elig_StatHol_Pay_Calc_AB;
                END
            ELSE
                BEGIN
                    SET @Result = @PRIMARY_KEY_ERROR; -- PK Already exists.
                END;
        END;

    RETURN @Result;

Open in new window


btw, why that dup check? Shouldn't be Pay_Sum_Grp the only primary key column?

And a second one which is a wrapper to be called from Excel. Here you can to your text to bit conversion and generate your desired result set.

p.s. you model seems not to be normalized..
0
 
valentinemhlangaAuthor Commented:
@ste5an We do not have a PK in this table and would be a good idea to do so.  I wanted to return some value when suceeds or fails . if I do not get anything back the spreadsheet will be saying there is something wrong with the query
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.