3

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on this column with a scalar valued function.

    CREATE TYPE [dbo].[Accounts] AS TABLE(
    [AccountName] [varchar](25) NOT NULL 
         CONSTRAINT CHK_AccountName CHECK (dbo.validateAccountName(AccountName) = 1),
    [Sales] [int] NOT NULL,
    [Returns] [int] NOT NULL
);

However I get a syntax error, when I do the same on a regular table it works. Based on MSFT documentation check constraints should be ok on table types. What am I missing here ? Any better approach to validate input on a table type ?

1

Although table types do allow unnamed CHECK constraints, it is not possible to use a user-defined function inside one.

The error message is:

Msg 2785 Level 16 State 1 Line 3
User-defined functions, user-defined aggregates, CLR types, and methods on CLR types are not allowed in expressions in this context.
Msg 1750 Level 16 State 0 Line 1
Could not create constraint. See previous errors.

See the db<>fiddle demo

| improve this answer | |
-2

Try this solution from https://stackoverflow.com/questions/53330114/user-defined-table-type-with-check-constraint

CREATE TYPE [dbo].[Accounts] AS TABLE(
    [AccountName] [varchar](25) NOT NULL 
         CHECK (dbo.validateAccountName(AccountName) = 1),
    [Sales] [int] NOT NULL,
    [Returns] [int] NOT NULL
);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.