Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
create store procedure for insert and perform the check before the insert statement, and if check is ok do the insert, if its not return message for invalid AccountName or something similar. –  dimitar Feb 6 at 10:10
2  
The CONSTRAINT CHK_AccountName is wrong. Table types can't have named constraints. Even removing it gives Invalid column name 'dbo'. though. I presume this isn't allowed. –  Martin Smith Feb 6 at 10:21
2  
@MartinSmith, I get at SQL-Fiddle: Schema Creation Failed: User-defined functions, user-defined aggregates, CLR types, and methods on CLR types are not allowed in expressions in this context.: –  ypercube Feb 6 at 10:24
    
@ypercube - Ah I hadn't bothered creating a dummy scalar UDF. Now I have I see the same thing. –  Martin Smith Feb 6 at 10:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.