T-SQL: How to raise an error on a user defined function (udf)

A common programming task is to create a user-defined function. The function can perform some tasks and raise an error if some validation fails.
Let us try to raise an error

CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT)
RETURNS INT
AS
BEGIN
    IF (@op2 = 0)
    BEGIN
    RAISERROR ('Division by zero.', 16, 1);
    END

    RETURN CAST(@op1 AS DECIMAL(18, 8)) /@op2
END

But this approach gives the following error:

Msg 443, Level 16, State 14, Procedure Division, Line 7 [Batch Start Line 0]
Invalid use of a side-effecting operator 'RAISERROR' within a function.

So another approach is needed.
Let us try the THROW statement

CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT)
RETURNS INT
AS
BEGIN
    IF (@op2 = 0)
    BEGIN
    ;THROW 51000, 'Division by zero.',1;
    END

    RETURN CAST(@op1 AS DECIMAL(18, 8)) /@op2
END

But the result is the same as the RAISERROR:

Msg 443, Level 16, State 14, Procedure Division, Line 7 [Batch Start Line 0]
Invalid use of a side-effecting operator 'THROW' within a function.

Having tried all the known solutions to throw an exception in Sql Server, an alternative is needed.
Let's try a cast conversion error:

CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT)
RETURNS DECIMAL(18, 8)
AS
BEGIN
    IF (@op2 = 0)
    BEGIN
    RETURN CAST('Division by zero.' AS INT);
    END

    RETURN CAST(@op1 AS DECIMAL(18, 8)) /  CAST(@op2 AS DECIMAL(18, 8))
END

The function is now created with success.
Let us see the result of calling the function with a division by zero:

SELECT dbo.Division(1, 0)

The following exception is thrown:

Msg 245, Level 16, State 1, Line 14
Conversion failed when converting the varchar value 'Division by zero.' to data type int.


It's not the ideal solution, but it allows us to generate a exception on a T-SQL function.

How to backup all the user databases

The database administrator must backup periodically the databases on the Sql Servers.

The process is tedious and must be automated with a script to avoid forgetting any database.


The simple script can be scheduled on the Sql Server agent.

DECLARE @backupName VARCHAR(255) -- database backup name  
DECLARE @databaseName VARCHAR(255) -- database name  
DECLARE @path VARCHAR(256) -- Folder for the for backup files  
DECLARE @fileName VARCHAR(256) -- file name for generating the backup  
DECLARE @databaseExclusions TABLE (DatabaseName VARCHAR(255)) -- Databases to exclude the backup

-- Input parameters
SET @path = 'D:\Backup\2\'  
INSERT INTO @databaseExclusions (DatabaseName)
VALUES
('tempdb'),('master'),('model'),('msdb'),('AdventureWorks')


DECLARE database_cursor CURSOR FOR  
SELECT top 5 name
    FROM sys.databases 
    WHERE [state] <> 6 -- OFFLINE
    AND owner_sid != 1 -- User user database
    AND name NOT IN (SELECT DatabaseName FROM @databaseExclusions)
 
OPEN database_cursor   
FETCH NEXT FROM database_cursor INTO @databaseName   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @databaseName;  
   SET @backupName = @databaseName + '-Full Database Backup';

   BACKUP DATABASE @databaseName TO DISK = @fileName WITH NOFORMAT, INIT,  NAME = @backupName, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
   
   FETCH NEXT FROM database_cursor INTO @databaseName   
END   

 
CLOSE database_cursor   
DEALLOCATE database_cursor