How to Convert RAISERROR to THROW in SQL Server 2016

error handlingexceptionraiserrorsql serversql-server-2016

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?

Best Answer

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
Related Question