How to restrict insertion depends on condition?

  • 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?

  • 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’! **
  • 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/

  • 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.

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

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