How to validate a column using stored procedure

I have a column name Room_type ,the valid room types are bedroom,dining room,living room,kitchen,bathroom.

I have to validate the room_type column such that only valid room types are allowed.

re: How to validate a column using stored procedure

The problem is that invalid data has been entered.I am assuming at the front end.
This can be rendered impossible by this design
You can easily migrate to this design and in the process filter out the invalid data.
Or maybe not. Let me know

re: How to validate a column using stored procedure

Then create a relationship (FK - to - PK) between the two table.

-- CK

re: How to validate a column using stored procedure

its not like that i have to write a procedure where in i should be able to insert a room and while inserting i should see that room type is valid.

valid types i have listed already.

I cannot use one more table

re: How to validate a column using stored procedure

It's better if you create a CONSTRAINT.

If some inserted a row on your table without using your SP, it might still have invalid values.

-- CK

re: How to validate a column using stored procedure

can you tell me how to create constraint for that because the valid room types are around 4

re: How to validate a column using stored procedure

Is there any way to validate that column without using different table and constraint

re: How to validate a column using stored procedure

Here are some readings.

-- CK

re: How to validate a column using stored procedure

How does that concept of constraints help solve my problem

see my problem is whenever i try to insert a room_type into a table it shouldn't allow any other types other than that i have listed.

what can i do to solve this.

will using a constraint solve this out.

if yes what type of constraint and give me an example

re: How to validate a column using stored procedure

A CHECK CONSTRAINT can be created for a field to ensure that the condition has to be fulfilled first before SQL SERVER allows insert/update on the table. So if you have a constraint on your room_type column that says all valid values should be IN ('BEDROOM','DINNING'), then only these two values will be inserted on your column, otherwise it will not allow the insert or update of the entire record.

-- CK

re: How to validate a column using stored procedure

plz tell me how to write it

i mean syntax

re: How to validate a column using stored procedure

how to write a check constraint expression to solve my problem