|
|
You're in the
sql server programming
group:If statement in a trigger
sql server programming:
[quoted text, click to view]
On Sep 11, 11:35 am, "Tamer Ibrahim" <camel7...@yahoo.co.uk> wrote: > Hi, > > I have If condition that evaluate always to ture and I can't figure out why > . Any help will be most appeciated. > > I have placed a comment next to the If statement mentioned ... > > ALTER TRIGGER TriggerInsertHotel > > ON dbo.Hotel_Res > > FOR INSERT /*, UPDATE DELETE */ > > AS > > /* IF UPDATE () ...*/ > > Declare @File_ID varchar(10),@var1 varchar(10),@var2 varchar(10), > > @From_Date datetime, > > @ExistedFrom_Date datetime, > > @To_Date datetime, > > @ExistedTo_Date datetime, > > @Pax_No smallint, > > @Reserved_NO smallint, > > @ExistedReserved_NO smallint > > select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date , > @Reserved_NO = (Adults + Kids1 + Kids2) > > from inserted > > Select @Pax_No = Pax_No > > from Files_Info > > where [File_ID] = @File_ID > > set @var1=null > > set @var2=null > > select @var1=[File_ID] from Hotel_Res Where [File_ID] = @File_ID > > select @var2=[File_ID] from NCRS_Res Where [File_ID] = @File_ID > > IF ((@var1 != null) or (@var2 != null)) > //This if condition always evaluate to ture !!! > > Begin > > Declare OverLap_Cursor1 Cursor > > for > > Select From_Date, To_Date , (Adults + Kids1 + Kids2) from Hotel_Res where > [File_ID] = @File_ID > > Union > > Select From_Date, To_Date , (Adults + Kids1 + Kids2) from NCRS_Res where > [File_ID] = @File_ID > > Open OverLap_Cursor1 > > fetch next > > from OverLap_Cursor1 > > into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO > > While (@@FETCH_STATUS = 0) > > Begin > > If ( (@From_Date > @ExistedFrom_Date and @From_Date < @ExistedTo_Date) or > (@To_Date > @ExistedFrom_Date and @To_Date < @ExistedTo_Date) or > (@Reserved_NO > (@Pax_No - @ExistedReserved_NO))) > > Begin > > raiserror('Date overlap or reservation pax number violation.', 10, 1) > > rollback tran > > Return > > End > > fetch next > > from OverLap_Cursor1 > > into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO > > End > > close OverLap_Cursor1 > > deallocate OverLap_Cursor1 > > End > > Return There is no need to write a cursor to detect overlaps, it is easy to accomplish using set-based logic.
[quoted text, click to view]
>IF ((@var1 != null) or (@var2 != null)) IF ((@var1 is not null) or (@var2 is not null))Also don't use cursor. - Shiju [quoted text, click to view] On Sep 11, 9:35 pm, "Tamer Ibrahim" <camel7...@yahoo.co.uk> wrote: > Hi, > > I have If condition that evaluate always to ture and I can't figure out why > . Any help will be most appeciated. > > I have placed a comment next to the If statement mentioned ... > > ALTER TRIGGER TriggerInsertHotel > > ON dbo.Hotel_Res > > FOR INSERT /*, UPDATE DELETE */ > > AS > > /* IF UPDATE () ...*/ > > Declare @File_ID varchar(10),@var1 varchar(10),@var2 varchar(10), > > @From_Date datetime, > > @ExistedFrom_Date datetime, > > @To_Date datetime, > > @ExistedTo_Date datetime, > > @Pax_No smallint, > > @Reserved_NO smallint, > > @ExistedReserved_NO smallint > > select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date , > @Reserved_NO = (Adults + Kids1 + Kids2) > > from inserted > > Select @Pax_No = Pax_No > > from Files_Info > > where [File_ID] = @File_ID > > set @var1=null > > set @var2=null > > select @var1=[File_ID] from Hotel_Res Where [File_ID] = @File_ID > > select @var2=[File_ID] from NCRS_Res Where [File_ID] = @File_ID > > IF ((@var1 != null) or (@var2 != null)) > //This if condition always evaluate to ture !!! > > Begin > > Declare OverLap_Cursor1 Cursor > > for > > Select From_Date, To_Date , (Adults + Kids1 + Kids2) from Hotel_Res where > [File_ID] = @File_ID > > Union > > Select From_Date, To_Date , (Adults + Kids1 + Kids2) from NCRS_Res where > [File_ID] = @File_ID > > Open OverLap_Cursor1 > > fetch next > > from OverLap_Cursor1 > > into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO > > While (@@FETCH_STATUS = 0) > > Begin > > If ( (@From_Date > @ExistedFrom_Date and @From_Date < @ExistedTo_Date) or > (@To_Date > @ExistedFrom_Date and @To_Date < @ExistedTo_Date) or > (@Reserved_NO > (@Pax_No - @ExistedReserved_NO))) > > Begin > > raiserror('Date overlap or reservation pax number violation.', 10, 1) > > rollback tran > > Return > > End > > fetch next > > from OverLap_Cursor1 > > into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO > > End > > close OverLap_Cursor1 > > deallocate OverLap_Cursor1 > > End > > Return
It isn't a matter of "can't". It is simply that cursors are HORRIBLE from a
performance standpoint and should be avoided if at all possible. They almost always ARE avoidable, btw. I didn't go into your code in-depth, but on first blush I would say that your logic can be rewritten to use set-based logic. Oh, and if you DO use cursors, they should almost always be defined as FAST_FORWARD, which is optimized by the sql server engine. Oh, and if you cannot have duplicates in your UNION set, you should use UNION ALL, which avoids a sort/distinct operation under the covers - again leading to lower performance. -- TheSQLGuru President Indicium Resources, Inc. [quoted text, click to view] "Tamer Ibrahim" <camel75eg@yahoo.co.uk> wrote in message news:%23dSnM$J9HHA.4712@TK2MSFTNGP04.phx.gbl... > Why can't I use a cursor ? I need them to loop over the rows returned from > the two tables resulting from the union of the select statements .... > > "Shiju Samuel" <shiju.samuel@gmail.com> wrote in message > news:1189532860.200156.251870@r34g2000hsd.googlegroups.com... >> >IF ((@var1 != null) or (@var2 != null)) >> IF ((@var1 is not null) or (@var2 is not null)) >> >> Also don't use cursor. >> - >> Shiju >> >> On Sep 11, 9:35 pm, "Tamer Ibrahim" <camel7...@yahoo.co.uk> wrote: >>> Hi, >>> >>> I have If condition that evaluate always to ture and I can't figure out >>> why >>> . Any help will be most appeciated. >>> >>> I have placed a comment next to the If statement mentioned ... >>> >>> ALTER TRIGGER TriggerInsertHotel >>> >>> ON dbo.Hotel_Res >>> >>> FOR INSERT /*, UPDATE DELETE */ >>> >>> AS >>> >>> /* IF UPDATE () ...*/ >>> >>> Declare @File_ID varchar(10),@var1 varchar(10),@var2 varchar(10), >>> >>> @From_Date datetime, >>> >>> @ExistedFrom_Date datetime, >>> >>> @To_Date datetime, >>> >>> @ExistedTo_Date datetime, >>> >>> @Pax_No smallint, >>> >>> @Reserved_NO smallint, >>> >>> @ExistedReserved_NO smallint >>> >>> select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date >>> , >>> @Reserved_NO = (Adults + Kids1 + Kids2) >>> >>> from inserted >>> >>> Select @Pax_No = Pax_No >>> >>> from Files_Info >>> >>> where [File_ID] = @File_ID >>> >>> set @var1=null >>> >>> set @var2=null >>> >>> select @var1=[File_ID] from Hotel_Res Where [File_ID] = @File_ID >>> >>> select @var2=[File_ID] from NCRS_Res Where [File_ID] = @File_ID >>> >>> IF ((@var1 != null) or (@var2 != null)) >>> //This if condition always evaluate to ture !!! >>> >>> Begin >>> >>> Declare OverLap_Cursor1 Cursor >>> >>> for >>> >>> Select From_Date, To_Date , (Adults + Kids1 + Kids2) from Hotel_Res >>> where >>> [File_ID] = @File_ID >>> >>> Union >>> >>> Select From_Date, To_Date , (Adults + Kids1 + Kids2) from NCRS_Res where >>> [File_ID] = @File_ID >>> >>> Open OverLap_Cursor1 >>> >>> fetch next >>> >>> from OverLap_Cursor1 >>> >>> into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO >>> >>> While (@@FETCH_STATUS = 0) >>> >>> Begin >>> >>> If ( (@From_Date > @ExistedFrom_Date and @From_Date < @ExistedTo_Date) >>> or >>> (@To_Date > @ExistedFrom_Date and @To_Date < @ExistedTo_Date) or >>> (@Reserved_NO > (@Pax_No - @ExistedReserved_NO))) >>> >>> Begin >>> >>> raiserror('Date overlap or reservation pax number violation.', 10, 1) >>> >>> rollback tran >>> >>> Return >>> >>> End >>> >>> fetch next >>> >>> from OverLap_Cursor1 >>> >>> into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO >>> >>> End >>> >>> close OverLap_Cursor1 >>> >>> deallocate OverLap_Cursor1 >>> >>> End >>> >>> Return >> >> > >
First, do not test for NULL using =, <> or != as they don't work. Use
IS NULL or IS NOT NULL. [quoted text, click to view] >IF ((@var1 != null) or (@var2 != null)) IF @var1 IS NOT NULL or @var2 IS NOT NULL Second, your trigger code can not work if more than one row was affected. Triggers fire once per command, not once per row. If more than one row is INSERTed then the inserted table will have more than one row. The following will not work in that case. [quoted text, click to view] >select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date , >@Reserved_NO = (Adults + Kids1 + Kids2) >from inserted Third, as has been said by others there doesn't appear to be anything here that requires a cursor. I have not gone to the trouble of sorting out exactly what the code is trying to do, but here is something that I hope will give you something to think about. SELECT * FROM Table1 as A WHERE EXISTS (SELECT * FROM Table2 as B WHERE A.Customer = B.Customer AND A.TranDate BETWEEN B.StartDate and B.EndDate) The idea with SQL is to think of sets operations rather than row-at-a-time processing. Roy Harvey Beacon Falls, CT On Tue, 11 Sep 2007 19:35:43 +0300, "Tamer Ibrahim" [quoted text, click to view] <camel75eg@yahoo.co.uk> wrote:
>Hi, > >I have If condition that evaluate always to ture and I can't figure out why >. Any help will be most appeciated. > >I have placed a comment next to the If statement mentioned ... > > > >ALTER TRIGGER TriggerInsertHotel > >ON dbo.Hotel_Res > >FOR INSERT /*, UPDATE DELETE */ > >AS > >/* IF UPDATE () ...*/ > >Declare @File_ID varchar(10),@var1 varchar(10),@var2 varchar(10), > >@From_Date datetime, > >@ExistedFrom_Date datetime, > >@To_Date datetime, > >@ExistedTo_Date datetime, > >@Pax_No smallint, > >@Reserved_NO smallint, > >@ExistedReserved_NO smallint > > >select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date , >@Reserved_NO = (Adults + Kids1 + Kids2) > >from inserted > >Select @Pax_No = Pax_No > >from Files_Info > >where [File_ID] = @File_ID > >set @var1=null > >set @var2=null > >select @var1=[File_ID] from Hotel_Res Where [File_ID] = @File_ID > >select @var2=[File_ID] from NCRS_Res Where [File_ID] = @File_ID > >IF ((@var1 != null) or (@var2 != null)) >//This if condition always evaluate to ture !!! > >Begin > >Declare OverLap_Cursor1 Cursor > >for > >Select From_Date, To_Date , (Adults + Kids1 + Kids2) from Hotel_Res where >[File_ID] = @File_ID > >Union > >Select From_Date, To_Date , (Adults + Kids1 + Kids2) from NCRS_Res where >[File_ID] = @File_ID > >Open OverLap_Cursor1 > >fetch next > >from OverLap_Cursor1 > >into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO > >While (@@FETCH_STATUS = 0) > >Begin > >If ( (@From_Date > @ExistedFrom_Date and @From_Date < @ExistedTo_Date) or >(@To_Date > @ExistedFrom_Date and @To_Date < @ExistedTo_Date) or >(@Reserved_NO > (@Pax_No - @ExistedReserved_NO))) > >Begin > >raiserror('Date overlap or reservation pax number violation.', 10, 1) > >rollback tran > >Return > >End > > > >fetch next > >from OverLap_Cursor1 > >into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO > >End > >close OverLap_Cursor1 > >deallocate OverLap_Cursor1 > >End > >Return >
Hi,
I have If condition that evaluate always to ture and I can't figure out why .. Any help will be most appeciated. I have placed a comment next to the If statement mentioned ... ALTER TRIGGER TriggerInsertHotel ON dbo.Hotel_Res FOR INSERT /*, UPDATE DELETE */ AS /* IF UPDATE () ...*/ Declare @File_ID varchar(10),@var1 varchar(10),@var2 varchar(10), @From_Date datetime, @ExistedFrom_Date datetime, @To_Date datetime, @ExistedTo_Date datetime, @Pax_No smallint, @Reserved_NO smallint, @ExistedReserved_NO smallint select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date , @Reserved_NO = (Adults + Kids1 + Kids2) from inserted Select @Pax_No = Pax_No from Files_Info where [File_ID] = @File_ID set @var1=null set @var2=null select @var1=[File_ID] from Hotel_Res Where [File_ID] = @File_ID select @var2=[File_ID] from NCRS_Res Where [File_ID] = @File_ID IF ((@var1 != null) or (@var2 != null)) //This if condition always evaluate to ture !!! Begin Declare OverLap_Cursor1 Cursor for Select From_Date, To_Date , (Adults + Kids1 + Kids2) from Hotel_Res where [File_ID] = @File_ID Union Select From_Date, To_Date , (Adults + Kids1 + Kids2) from NCRS_Res where [File_ID] = @File_ID Open OverLap_Cursor1 fetch next from OverLap_Cursor1 into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO While (@@FETCH_STATUS = 0) Begin If ( (@From_Date > @ExistedFrom_Date and @From_Date < @ExistedTo_Date) or (@To_Date > @ExistedFrom_Date and @To_Date < @ExistedTo_Date) or (@Reserved_NO > (@Pax_No - @ExistedReserved_NO))) Begin raiserror('Date overlap or reservation pax number violation.', 10, 1) rollback tran Return End fetch next from OverLap_Cursor1 into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO End close OverLap_Cursor1 deallocate OverLap_Cursor1 End Return
Why can't I use a cursor ? I need them to loop over the rows returned from
the two tables resulting from the union of the select statements .... [quoted text, click to view] "Shiju Samuel" <shiju.samuel@gmail.com> wrote in message news:1189532860.200156.251870@r34g2000hsd.googlegroups.com... > >IF ((@var1 != null) or (@var2 != null)) > IF ((@var1 is not null) or (@var2 is not null)) > > Also don't use cursor. > - > Shiju > > On Sep 11, 9:35 pm, "Tamer Ibrahim" <camel7...@yahoo.co.uk> wrote: >> Hi, >> >> I have If condition that evaluate always to ture and I can't figure out >> why >> . Any help will be most appeciated. >> >> I have placed a comment next to the If statement mentioned ... >> >> ALTER TRIGGER TriggerInsertHotel >> >> ON dbo.Hotel_Res >> >> FOR INSERT /*, UPDATE DELETE */ >> >> AS >> >> /* IF UPDATE () ...*/ >> >> Declare @File_ID varchar(10),@var1 varchar(10),@var2 varchar(10), >> >> @From_Date datetime, >> >> @ExistedFrom_Date datetime, >> >> @To_Date datetime, >> >> @ExistedTo_Date datetime, >> >> @Pax_No smallint, >> >> @Reserved_NO smallint, >> >> @ExistedReserved_NO smallint >> >> select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date , >> @Reserved_NO = (Adults + Kids1 + Kids2) >> >> from inserted >> >> Select @Pax_No = Pax_No >> >> from Files_Info >> >> where [File_ID] = @File_ID >> >> set @var1=null >> >> set @var2=null >> >> select @var1=[File_ID] from Hotel_Res Where [File_ID] = @File_ID >> >> select @var2=[File_ID] from NCRS_Res Where [File_ID] = @File_ID >> >> IF ((@var1 != null) or (@var2 != null)) >> //This if condition always evaluate to ture !!! >> >> Begin >> >> Declare OverLap_Cursor1 Cursor >> >> for >> >> Select From_Date, To_Date , (Adults + Kids1 + Kids2) from Hotel_Res where >> [File_ID] = @File_ID >> >> Union >> >> Select From_Date, To_Date , (Adults + Kids1 + Kids2) from NCRS_Res where >> [File_ID] = @File_ID >> >> Open OverLap_Cursor1 >> >> fetch next >> >> from OverLap_Cursor1 >> >> into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO >> >> While (@@FETCH_STATUS = 0) >> >> Begin >> >> If ( (@From_Date > @ExistedFrom_Date and @From_Date < @ExistedTo_Date) or >> (@To_Date > @ExistedFrom_Date and @To_Date < @ExistedTo_Date) or >> (@Reserved_NO > (@Pax_No - @ExistedReserved_NO))) >> >> Begin >> >> raiserror('Date overlap or reservation pax number violation.', 10, 1) >> >> rollback tran >> >> Return >> >> End >> >> fetch next >> >> from OverLap_Cursor1 >> >> into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO >> >> End >> >> close OverLap_Cursor1 >> >> deallocate OverLap_Cursor1 >> >> End >> >> Return > >
I'm using this technics to detect date overlapping between dates recorded in
two tables. What set-based logic I can use instead ? [quoted text, click to view] "Alex Kuznetsov" <alkuzo@gmail.com> wrote in message news:1189532438.537071.294850@w3g2000hsg.googlegroups.com... > On Sep 11, 11:35 am, "Tamer Ibrahim" <camel7...@yahoo.co.uk> wrote: >> Hi, >> >> I have If condition that evaluate always to ture and I can't figure out >> why >> . Any help will be most appeciated. >> >> I have placed a comment next to the If statement mentioned ... >> >> ALTER TRIGGER TriggerInsertHotel >> >> ON dbo.Hotel_Res >> >> FOR INSERT /*, UPDATE DELETE */ >> >> AS >> >> /* IF UPDATE () ...*/ >> >> Declare @File_ID varchar(10),@var1 varchar(10),@var2 varchar(10), >> >> @From_Date datetime, >> >> @ExistedFrom_Date datetime, >> >> @To_Date datetime, >> >> @ExistedTo_Date datetime, >> >> @Pax_No smallint, >> >> @Reserved_NO smallint, >> >> @ExistedReserved_NO smallint >> >> select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date , >> @Reserved_NO = (Adults + Kids1 + Kids2) >> >> from inserted >> >> Select @Pax_No = Pax_No >> >> from Files_Info >> >> where [File_ID] = @File_ID >> >> set @var1=null >> >> set @var2=null >> >> select @var1=[File_ID] from Hotel_Res Where [File_ID] = @File_ID >> >> select @var2=[File_ID] from NCRS_Res Where [File_ID] = @File_ID >> >> IF ((@var1 != null) or (@var2 != null)) >> //This if condition always evaluate to ture !!! >> >> Begin >> >> Declare OverLap_Cursor1 Cursor >> >> for >> >> Select From_Date, To_Date , (Adults + Kids1 + Kids2) from Hotel_Res where >> [File_ID] = @File_ID >> >> Union >> >> Select From_Date, To_Date , (Adults + Kids1 + Kids2) from NCRS_Res where >> [File_ID] = @File_ID >> >> Open OverLap_Cursor1 >> >> fetch next >> >> from OverLap_Cursor1 >> >> into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO >> >> While (@@FETCH_STATUS = 0) >> >> Begin >> >> If ( (@From_Date > @ExistedFrom_Date and @From_Date < @ExistedTo_Date) or >> (@To_Date > @ExistedFrom_Date and @To_Date < @ExistedTo_Date) or >> (@Reserved_NO > (@Pax_No - @ExistedReserved_NO))) >> >> Begin >> >> raiserror('Date overlap or reservation pax number violation.', 10, 1) >> >> rollback tran >> >> Return >> >> End >> >> fetch next >> >> from OverLap_Cursor1 >> >> into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO >> >> End >> >> close OverLap_Cursor1 >> >> deallocate OverLap_Cursor1 >> >> End >> >> Return > > There is no need to write a cursor to detect overlaps, it is easy to > accomplish using set-based logic. >
Hi,
During the last two days, I tried to figure out how I can replace my cursor with the select staement you gave me but I failed ! [quoted text, click to view] "Roy Harvey" <roy_harvey@snet.net> wrote in message news:6ende3hggdontulalqnnltvmhnc9kejbi6@4ax.com... > First, do not test for NULL using =, <> or != as they don't work. Use > IS NULL or IS NOT NULL. > >>IF ((@var1 != null) or (@var2 != null)) > > IF @var1 IS NOT NULL or @var2 IS NOT NULL > > Second, your trigger code can not work if more than one row was > affected. Triggers fire once per command, not once per row. If more > than one row is INSERTed then the inserted table will have more than > one row. The following will not work in that case. > >>select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date , >>@Reserved_NO = (Adults + Kids1 + Kids2) >>from inserted > > Third, as has been said by others there doesn't appear to be anything > here that requires a cursor. I have not gone to the trouble of > sorting out exactly what the code is trying to do, but here is > something that I hope will give you something to think about. > > SELECT * > FROM Table1 as A > WHERE EXISTS > (SELECT * FROM Table2 as B > WHERE A.Customer = B.Customer > AND A.TranDate BETWEEN B.StartDate and B.EndDate) > > The idea with SQL is to think of sets operations rather than > row-at-a-time processing. > > Roy Harvey > Beacon Falls, CT > > On Tue, 11 Sep 2007 19:35:43 +0300, "Tamer Ibrahim" > <camel75eg@yahoo.co.uk> wrote: > >>Hi, >> >>I have If condition that evaluate always to ture and I can't figure out >>why >>. Any help will be most appeciated. >> >>I have placed a comment next to the If statement mentioned ... >> >> >> >>ALTER TRIGGER TriggerInsertHotel >> >>ON dbo.Hotel_Res >> >>FOR INSERT /*, UPDATE DELETE */ >> >>AS >> >>/* IF UPDATE () ...*/ >> >>Declare @File_ID varchar(10),@var1 varchar(10),@var2 varchar(10), >> >>@From_Date datetime, >> >>@ExistedFrom_Date datetime, >> >>@To_Date datetime, >> >>@ExistedTo_Date datetime, >> >>@Pax_No smallint, >> >>@Reserved_NO smallint, >> >>@ExistedReserved_NO smallint >> >> >>select @File_ID = [File_ID], @From_Date = From_Date, @To_Date = To_Date , >>@Reserved_NO = (Adults + Kids1 + Kids2) >> >>from inserted >> >>Select @Pax_No = Pax_No >> >>from Files_Info >> >>where [File_ID] = @File_ID >> >>set @var1=null >> >>set @var2=null >> >>select @var1=[File_ID] from Hotel_Res Where [File_ID] = @File_ID >> >>select @var2=[File_ID] from NCRS_Res Where [File_ID] = @File_ID >> >>IF ((@var1 != null) or (@var2 != null)) >>//This if condition always evaluate to ture !!! >> >>Begin >> >>Declare OverLap_Cursor1 Cursor >> >>for >> >>Select From_Date, To_Date , (Adults + Kids1 + Kids2) from Hotel_Res where >>[File_ID] = @File_ID >> >>Union >> >>Select From_Date, To_Date , (Adults + Kids1 + Kids2) from NCRS_Res where >>[File_ID] = @File_ID >> >>Open OverLap_Cursor1 >> >>fetch next >> >>from OverLap_Cursor1 >> >>into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO >> >>While (@@FETCH_STATUS = 0) >> >>Begin >> >>If ( (@From_Date > @ExistedFrom_Date and @From_Date < @ExistedTo_Date) or >>(@To_Date > @ExistedFrom_Date and @To_Date < @ExistedTo_Date) or >>(@Reserved_NO > (@Pax_No - @ExistedReserved_NO))) >> >>Begin >> >>raiserror('Date overlap or reservation pax number violation.', 10, 1) >> >>rollback tran >> >>Return >> >>End >> >> >> >>fetch next >> >>from OverLap_Cursor1 >> >>into @ExistedFrom_Date, @ExistedTo_Date, @ExistedReserved_NO >> >>End >> >>close OverLap_Cursor1 >> >>deallocate OverLap_Cursor1 >> >>End >> >>Return >> >>
If you post table DDL, sample data and expected results, someone can help you
design a set-based solution. See this article for details: http://www.aspfaq.com/etiquette.asp?id=5006 ML --- Matija Lah, SQL Server MVP
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |