Avatar of rutledgj
rutledgj 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?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
rutledgj

8/22/2022 - Mon
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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
rutledgj

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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

ASKER
rutledgj

For some reason this seems to work although I don't understand the difference.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.