Validation between 2 Parameter

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

General SQL Server Forums

New to SQL Server Programming

Validation between 2 Parameter

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2010-02-24 : 20:21:32

I have a table schedule

schedule
------------
schedid (4)
secid int(4)
days varchar(50)
timein datetime
timeuot datetime


   declare @days varchar(20) 
   declare @timein datetime
   declare @timeout datetime
   select @days=e.days,@timein=e.timein,@timeout=e.timeout from inserted e

 if @days='Monday'
      begin
         if exists(Select * from schedule where days in('Monday','Monday and Wednesday') and @timein between timein and timeout)
                begin
                    raiserror conflict time
                    rollback transaction
                end
      end
 if @days='Monday'
      begin
         if exists(Select * from schedule where days in('Tuesday','Tuesday and Thursday') and @timein between timein and timeout)
                begin
                    raiserror conflict time
                    rollback transaction
                end
      end

i want to build the trigger that will not conflict the time schedule, where i sent to parameter..... parameter @timein and @timeout

finding the truth
making a maze on my mind....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 22:41:12

[code]
if exists
(
select *
from inserted i
where exists
(
select *
from schedule x
where x.days like i.days + '%'
and i.timein between x.timein and x.timeout
)
)
begin
raiserror conflict time
rollback transaction
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

wormz666
Posting Yak Master

110 Posts

Posted - 2010-02-24 : 23:34:17

how about if send parameter
timein 12:00PM
timeout 5:00PM
how can i validate on the schedule table...

finding the truth
making a maze on my mind....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 23:40:32

just check for existence of it in the schedule table


        exists
        (
                   select *
                   from   schedule x
                   where  x.days = 'something'
                   and    @timein between x.timein and x.timeout
        )

you might also need to check for @timeout

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:16:20

what according to use represents a conflict?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources