Validation on more than one column Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Validation on more than one column Help

Author

Topic

learntsql

524 Posts

Posted - 2010-12-22 : 00:00:22

Hi All,
I have table defined with 5 columns.
How to validate data entering into table on more than onw column at a time.
for example, I have Emp table whith EID,Ename,Age,Rating,DID

Now I need to apply the condition like when Age >= 20 and Rating > 4
then only record must be entered.

TIA.

Sachin.Nand

2937 Posts

Posted - 2010-12-22 : 01:25:37

quote:
then only record must be entered.

Did not understand what you mean?

PBUH

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-22 : 02:43:03

How many conditions you want to apply on column ?

Example:
Age >= 20 and Rating > 4
or Age >= 25 and Rating > 5
etc..

One way is to use triggers but again trigger have lot of pros and cons.

latch
Yak Posting Veteran

62 Posts

Posted - 2010-12-22 : 09:51:04

Hi learntsql,

i understood from your post that you need to check the value of age and rating before inserting values into the table.

If iam correct then i think below script might be helpful,

CREATE TABLE emp(EID int,Ename varchar(6),Age int,Rating int,DID int)
GO
CREATE FUNCTION CheckAgeandRating()
RETURNS int
AS
BEGIN
DECLARE @retval int
select @retval = case when Age > 20 and Rating > 4 then 1
else 0
end from emp
RETURN @retval
END;
GO
ALTER TABLE emp
ADD CONSTRAINT chkAgeandrating CHECK (dbo.CheckAgeandRating() = 1 );
GO

------
Validation:
insert into emp values(1,'abc',22,5,11)
insert into emp values(2,'xyz',19,4,2)
insert into emp values(3,'xxx',23,2,2)
insert into emp values(4,'web',19,4,2)
----
select * from emp;

hope it solved your problem.

thanks,
latch

learntsql

524 Posts

Posted - 2010-12-22 : 23:15:37

ThankQ very much guys.
Instead of function calling can we impliment function definition directly.?
TIA.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-23 : 03:46:30

quote:

Instead of function calling can we impliment function definition directly.?

Try:

create table emp
(
Eid Int,
Ename varchar(50),
Age Int ,
Rating Int,
Did Int,
constraint ck_Age_Rtg
Check (Case When emp.Age>=20 and emp.Rating >= 4 Then 1 Else 0 End =1)
)

Insert into Emp
Values (1,'Test',21,4,0)

Insert into Emp
Values (1,'Test',19,4,0)

Select * from emp

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

learntsql

524 Posts

Posted - 2010-12-23 : 04:30:00

ThankQ all its worked fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources