CODEWITHSUNDEEP
sql-server
Arsalan
Arsalan

Reputation: 744

Raiserror SQL Server

I have an insert statement :

Insert into foo (id, value) 
Values (1, 5)

Is there any way to raise an error when value is for example -1

Insert into foo (Id, value)
Values (1, case when -1=-1 then raiserror() else 5 end)

Upvotes: 0

Views: 94

Answers (2)

Gul Ershad
Gul Ershad

Reputation: 1771

Raiseerror() method is supported in stored procedure.

Better to write insert query into stored procedure and apply validation like below:

IF (@value < 0) 
BEGIN
    RAISERROR('Invalid parameter: @value cannot be less than zero', 18, 0)
    RETURN
END

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You could try adding a check constraint, e.g.

CREATE TABLE dbo.foo
(
    id int primary key,
    value int NOT NULL CHECK (value >= 0)
)

If the table foo already exists, then you can use ALTER TABLE to add the constraint:

ALTER TABLE dbo.foo ADD CONSTRAINT ck_value CHECK (value >= 0)

Upvotes: 1

Related Questions