Please implement constraints for local variable declarations.

[Breck_Carter]

How many times have you wanted to code something like this?

DECLARE @upgrade_ok VARCHAR ( 1 ) NOT NULL DEFAULT 'Y' 
                       CHECK ( @upgrade_ok IN ( 'Y', 'N' ) );

If you're like me, lotsa times. In fact, quite a few times I have coded NOT NULL... on purpose, not by accident.

[thomas_duemesnil]

I type my variables currently with a DOMAIN. It would be great if the constraints would work in this case too.

CREATE DOMAIN "BOOLEAN" char(1) DEFAULT 'N' check(@column is null or(@column in( 'Y','N') ));

DECLARE @upgrade_ok BOOLEAN;

[VolkerBarth]

@TDuemesnil: You can shorten the check - CHECK constraints are only violated when the expression is FALSE, not when UNKNOWN. This is different to search conditions where only expressions that are TRUE get selected. Therefore the "@column is null or "-part can be omitted (though it might be more comprehensible).

[justin_willey · Accepted solution]

Yes please - tried to write that but yesterday before realising you can't do it!!

[Breck_Carter · Accepted solution]

Wondering where the 100 points came from? A random bounty, that's where!

[justin_willey · Accepted solution]

I was, and now I know - isn't knowledge a wonderful thing!

[VolkerBarth]

What about FKs, i.e. something like

CHECK (@value IN (SELECT pk FROM myTable));

I haven't had such requirement but that works with checks on columns and should then work with checks on local variables...

(Note, for columns, one would use a FOREIGN KEY constraint here, I know, but a check with a sub-select to a different table is possible, too, and senseful in some cases where a FK can't be used´).