asked on

Need help with a tsql query (stored proc)

I have a stored proc that simplified looks like this:

BEGIN TRY
IF @PKEY IS NULL
BEGIN
BEGIN TRANSACTION
       --Add resource
                  INSERT INTO dbo.ResourceExchange (columns) VALUES (data)
            COMMIT
            SET @ErrMsg = 'A new foreign resource was encountered for which a matching Resource table entry has not been defined.

            RAISERROR (@ErrMsg, 16, 1)
            RETURN

END
END TRY

BEGIN CATCH
              IF @ErrMsg is null OR @ErrMsg = ''
              BEGIN
                  SELECT @ErrMsg = 'An error occurred while adding event information for Org = ' + @Org + ', ChartNum = ' + @Chartnum
              END
              RAISERROR (@ErrMsg, 16, 1)
              RETURN

      END CATCH

My Problem is when @PKey = null that is doesn't set the error message to the first message.
I always get the errmsg in the catch section.

Can someone tell me why this is so and how to make it work as desired?

sammySeltzer

I would try something like this:

IF NOT EXISTS
( SELECT * FROM Timesheet_Hours WHERE PKey = @PKEY )
Begin transaction
....

HainKurt

comment out this line

INSERT INTO dbo.ResourceExchange (columns) VALUES (data)

and try again, what error message you get? if msg changes, the check the table, maybe the value already exist in table and you get PK violation error

sammySeltzer

In other words:

BEGIN TRY
       IF NOT EXISTS        
 (  SELECT * FROM ResourceExchange WHERE  PKey = @PKEY )  
        BEGIN
                BEGIN TRANSACTION
                      --Add resource
                  INSERT INTO dbo.ResourceExchange (columns) VALUES (data)
            COMMIT
            SET @ErrMsg = 'A new foreign resource was encountered for which a matching Resource table entry has not been defined.  
                                          
            RAISERROR (@ErrMsg, 16, 1)                        
            RETURN      
                     
        END
      END TRY


     BEGIN CATCH                  
                IF @ErrMsg is null OR @ErrMsg = ''
                BEGIN
                  SELECT @ErrMsg = 'An error occurred while adding event information for Org = ' + @Org + ', ChartNum = ' + @Chartnum 
                END
               RAISERROR (@ErrMsg, 16, 1)
               RETURN
                  
      END CATCH

Open in new window

rutledgj

ASKER

I guess I should have given you the statement just above this IF.

--see if there is already an entry with no resource id in the table.
                  SET @PKey = (SELECT Primarykey
                                     FROM dbo.ResourceExchange
                                     WHERE ForeignID = @ForeignResourceId
                                     AND Org = @Org
                                     AND ResourceID IS NULL)

If PKEY is null I want to do the insert and raise that error to notify the user that more info is needed for this record.

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saurabh Bhadauria

Just run below code and let us know what error message you are getting..

BEGIN TRY
        IF @PKEY IS NULL
        BEGIN
                BEGIN TRANSACTION
                      --Add resource
                  INSERT INTO dbo.ResourceExchange (columns) VALUES (data)
            COMMIT
            SET @ErrMsg = 'A new foreign resource was encountered for which a matching Resource table entry has not been defined.  
                                          
            RAISERROR (@ErrMsg, 16, 1)                        
            RETURN      
                     
        END
      END TRY

     BEGIN CATCH           
    declare @first_err nvarchar(max)
    set   @first_err=ERROR_MESSAGE ( )

                IF @ErrMsg is null OR @ErrMsg = ''
                BEGIN
                  SELECT @ErrMsg = ' [' + @first_err  +    ']    An error occurred while adding event information for Org = ' + @Org + ', ChartNum = ' + @Chartnum

                END
              print @ErrMsg
               RAISERROR (@ErrMsg, 16, 1)
               RETURN
                  
      END CATCH

Open in new window

rutledgj

ASKER

For some reason this seems to work although I don't understand the difference.