cancel
Showing results for 
Search instead for 
Did you mean: 

Please implement constraints for local variable declarations.

Breck_Carter
Participant
1,709

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
Participant
0 Kudos

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
Contributor

@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).

Accepted Solutions (1)

Accepted Solutions (1)

justin_willey
Participant

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

Breck_Carter
Participant
0 Kudos

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

justin_willey
Participant
0 Kudos

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

Answers (1)

Answers (1)

VolkerBarth
Contributor
0 Kudos

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´).