How to restrict insertion depends on condition?

Question

How to restrict insertion depends on condition?

raghuldrag

Hall of Fame

Points: 3160
More actions
August 19, 2013 at 12:16 am

#297007

Hi Friends,
create table travel
(
user_id varchar(10),
from varchar(10),
to varchar(10),
Lodging varchar(10),
foodBill varchar(10),
DailyAllowance varchar(10)
)
insert into travel (user_id,from,to,Lodging,foodbill,DailyAllowance) values('002','chennai','Banglore','4500','200',' ')
insert into travel (user_id,from,to,Lodging,foodbill,DailyAllowance) values('002','chennai','Banglore',' ',' ','500')
now mY requirement is when the user select Lodging & FoodBill Daily allowance ll be null.
if users choose DailyAllowance that Lodging & FoodBill ll be Null.
how to write procedure for these condition?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

First: define the smallest datatype possible for all of your columns. So you probably should change the ID to an INT datatype and the Lodging, FoodBill and DailyAllowance to a DECIMAL(10,2) datatype. Defining them all as VARCHAR(10) will not be the best practice and will give you lot of problems with implicit and explicit conversions.

Second: does the requirement state you have to use NULL or are you allowed to use 0?

You could define a CHECK CONSTRAINT on the table with something like "((Lodging + FoodBill) = 0 AND DailyAllowance > 0) OR ((Lodging + FoodBill) > 0 AND DailyAllowance = 0)"

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

the requirment that you have specified is not clear....

can you please provide some more detail information ?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

OTF SSCertifiable Points: 6104 More actions · Answer 3

This should amount to simple CASE Statements within your Procedure but you will have to provide a bit more information to get the help you need.