Syntax for coalescing to an error in SQL Server

Question

In some languages (C# for example), you can coalesce to a throw expression, E.G.

string someValue = MyFunctionThatMightReturnNull() ?? throw new Exception("null value");

Wondering if there is any syntax like this in SQL Server.

This is not valid syntax, but it illustrates the idea:

INSERT INTO "MyTable" ("MyColumn") 
VALUES (COALESCE(MyFunction(), THROW 50005, N'An error occurred', 1));

I understand that I can make the column NOT NULL, but in my use case the column should be NULL.

I also understand that I can use a block statement like

DECLARE @myVar VARCHAR(MAX);
SELECT @myVar = MyFunction();

IF (@myVar IS NULL)
BEGIN
    THROW 50005, N'An error occurred', 1;
END

INSERT INTO "MyTable" ("MyColumn") VALUES (@myVar);

While this works, it is limited because you can't use it with INSERT SELECT, E.G.

INSERT INTO "MyTable" ("MyColumn") 
    SELECT COALESCE("MyOtherTableColumn", THROW 50005, N'An error occurred', 1) 
    FROM "MyOtherTable";

Wondering if there is any syntax that accomplishes this.

Basically no, you have mentioned yourself the 2 options you have. T-SQL isn't comparable to a programming language like C#. — Dale K
– Dale K, Commented May 19 at 2:43
Redefine the function to throw exception instead of returning NULL when there is an error. — Vladimir Baranov
– Vladimir Baranov, Commented May 19 at 6:02
Why not make the column not null if it cannot accept bulls. But if you can't do that, there's no real syntax for it. Sometimes I have used something like : select isnull(somecolumn, cast('somecolumn cannot be null' as int)) or something like this which generates another error but still delivers the message inside it, mostly this is good for functions which cannot easily signal errors. — siggemannen
– siggemannen, Commented May 19 at 6:29
What are you actually trying to do here? This is clearly an XY Problem; explain the problem you're trying to solve rather than you it attempt to do something else that isn't permitted. — Thom A
– Thom A ♦, Commented May 19 at 7:49
Perhaps, for example, there is a scenario when MyColumn shouldn't be NULL, which can checked with a CONSTRAINT? — Thom A
– Thom A ♦, Commented May 19 at 8:08

tinazmu · Accepted Answer · 2025-05-21 00:42:45Z

Not very elegant, but: If you can't change the target table constraint, you can still create a local table variable (emulating your real target table, but with the constraints you want), and insert into that one first, then use the OUTPUT clause of that to insert into the target (permanent) table.

-- Say your table is like this
CREATE TABLE dbo.Targt (
    ID INT,
    Val VARCHAR(100) null,
    InsertedAt DATETIME DEFAULT GETDATE()
);

-- Declare a table variable with constraints
DECLARE @Trgt TABLE (
    ID INT PRIMARY KEY,
    Val VARCHAR(100) NOT NULL
);

-- Insert into the table variable, capture output and insert into real.
INSERT INTO @Trgt (ID, Val)
OUTPUT INSERTED.ID, INSERTED.Val, GETDATE()
INTO dbo.Targt (ID, Value, InsertedAt)
VALUES
    (1, 'Alpha'),
    (2, 'Beta'),
    (3, 'Gamma');

Any constraint violation will be flagged (against the @Trgt table)

Collectives™ on Stack Overflow

Syntax for coalescing to an error in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related