Is It possible to add same constraint to multiple tables?

Question

I have generated some code to do this in multiple tables. Error I get doing this is:

Variables are not allowed in the ALTER TABLE statement.

I understand that. The code I've generated :

DECLARE
@tableName VARCHAR(50) = 'myTable',
@sql NVARCHAR(100),
@parameter1 CHAR(1) = 'A',
@parameter2 CHAR(2) = 'I'

SELECT @sql = N'ALTER TABLE '+@tableName+'
               ADD CONSTRAINT CK_Status
               CHECK (Status in (@parameter1, @parameter2))'
EXEC sp_executesql @sql,N'@parameter1CHAR(1), @parameter2 CHAR(1)',@parameter1,@parameter2

I know it doesn't work. But I'd like if It is possible in some way, because I have a lot of tables to apply this.

NOTE: - I have a code to get tables and names and everything, I just need some idea to add the char values in the string.

Answer 1

You can add the same constraint, but not with the same name. A simple way is just to leave the name out. And, put the parameters directly in the query:

DECLARE
    @tableName VARCHAR(50) = 'myTable',
    @sql NVARCHAR(100),
    @parameter1 CHAR(1) = 'A',
    @parameter2 CHAR(2) = 'I';

SELECT @sql = N'ALTER TABLE @tableName
               ADD CONSTRAINT CHECK (Status in (''@parameter1'', ''@parameter2''))';

SET @sql = REPLACE(@sql, '@tableName', @tableName);
SET @sql = REPLACE(@sql, '@parameter1', @parameter1);
SET @sql = REPLACE(@sql, '@parameter2', @parameter2);

EXEC sp_executesql @sql;

If you have to deal with this constraint in many tables, you might consider having a table with valid values of status and using a foreign key constraint instead.