0

I have a function that checks is the file is present on disk in an specific location.

Using that function I am checking for the latest backup, in the code below. If we don't have a backup anymore, for a specific date, I want to raise an error.

Currently I am using raiserror but because of Deprecated raiserror detection I would like to use throw instead.

How can I do it?

this is part of my `stored procedure`:

IF @ServerNameToRestoreOn IS NULL
               THROW 50001, '@ServerNameToRestoreOn cannot be NULL - it must be the name of server\instance you want to restore the database to', 1

               IF @Database IS NULL
               THROW 50001, '@Database cannot be NULL - it must be the name of the database you want to restore', 1

               IF NOT EXISTS
                (
                    SELECT MAX(backup_set_id) 
                    FROM msdb.dbo.backupset a
              INNER JOIN msdb.dbo.backupmediafamily b
                     ON a.media_set_id = b.media_set_id

                    WHERE a.database_name = @Database
                      AND a.type='D'
                      AND a.is_copy_only = 0
                      AND a.backup_finish_date < @StopAt
                      AND master.dbo.fn_FileExists(b.physical_device_name) = 1 
                    GROUP BY database_name
                )
                BEGIN

                         SELECT @sql = 'The database called "%s" does not have a backup before' + CAST (@StopAt AS VARCHAR(20)) + @vCrlf + @vCrlf 
                         RAISERROR(@sql ,16,1,@Database)

                END 

How can I replace the raiserror by throw? I want to show the @Database and the @stopat in the error message

or, in better wording:

how do you pass THROW() a message where you can still use printf-style substitution like you can with RAISERROR?

  • 2
    So is the question how do you pass THROW() a message where you can still use printf-style substitution like you can with RAISERROR? – Aaron Bertrand Jun 27 at 18:01
2

I think this is what you mean? This is the closest you can get AFAIK.

--Catch any errors, include stored proc name for stack trace & cheeky link to SE
BEGIN CATCH;
       IF (XACT_STATE()) = -1 OR ((XACT_STATE()) = 1 AND (@@TRANCOUNT = 0 OR @@TRANCOUNT > 0))
          BEGIN
             ROLLBACK TRAN;
             DECLARE @errMsg NVARCHAR(MAX)= FORMATMESSAGE('%s: %s Try troubleshooting: http://dba.stackexchange.com/search?q=msg+%i', OBJECT_NAME(@@PROCID),ERROR_MESSAGE(),ERROR_NUMBER());
             THROW 51000, @errMsg, 1; --Throw error
          END
END CATCH
  • +1 that is exactly what I was looking for and was struggling to put together – Marcello Miorelli Jun 28 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.