Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to restrict insertion depends on condition? Expand / Collapse
Posted Monday, August 19, 2013 12:16 AM


Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:57 AM
Points: 179, Visits: 448
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?
Post #1485656
Posted Monday, August 19, 2013 1:04 AM



Group: General Forum Members
Last Login: Wednesday, January 28, 2015 3:45 AM
Points: 2,526, Visits: 3,052
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’! **
Post #1485663
Posted Monday, August 19, 2013 2:08 AM



Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 1,990, Visits: 2,431
the requirment that you have specified is not clear....
can you please provide some more detail information ?

To get quick answer follow this link:
Post #1485681
Posted Tuesday, August 20, 2013 3:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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.
Post #1486150
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse