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
Author
Message
Posted Monday, August 19, 2013 12:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:52 AM
Points: 160, Visits: 399
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 2,078, Visits: 2,410
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:50 PM
Points: 1,867, Visits: 2,275
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/
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