SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Condition Logic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 01/14/2013 :  02:02:40  Show Profile  Reply with Quote
I am using Above Query but it donot meat to the requirement i want to Check all the Conditions i mean if Condition 1 is true then it should go above also neither than Coming out of it
my Query is


Alter PROCEDURE InsManualData2
(
@EID int,
@Date Datetime,
@TimeIn Datetime,
@TimeOut Datetime,
@Remarks varchar(50),
@Comments varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS
(
SELECT * FROM ShortLeave2
WHERE EID = @EID
AND
--CheckTime = DateAdd(d, DateDiff(d, 0, Cast(CONVERT(VARCHAR(30),@Date,110) As datetime)), Cast(CONVERT(VARCHAR(10),@TIMEIN,108) as datetime))
CheckTime = DateAdd(d, DateDiff(d, 0, Cast(CONVERT(VARCHAR(30),@Date,110) As datetime)), Cast(CONVERT(VARCHAR(10),@TIMEIN,108) as datetime))
)

BEGIN
UPDATE ShortLeave2
SET Remarks = @Remarks,
Comments = @Comments
WHERE
EID = @EID
AND
CheckTime = DateAdd(d, DateDiff(d, 0, Cast(CONVERT(VARCHAR(30),@Date,110) As datetime)), Cast(CONVERT(VARCHAR(10),@TimeOut,108) as datetime))
END


ELSE IF EXISTS(SELECT * FROM ShortLeave2
WHERE EID = @EID
AND
CheckTime = @TimeOut
)

BEGIN
UPDATE ShortLeave2
SET Remarks = @Remarks,
Comments = @Comments
WHERE
EID = @EID
AND
CheckTime = @TimeOut
END


ELSE IF NOT EXISTS(SELECT * FROM SHORTLEAVE2
WHERE
EID = @EID
AND
CheckTime = DateAdd(d, DateDiff(d, 0, Cast(CONVERT(VARCHAR(30),@Date,110) As datetime)), Cast(CONVERT(VARCHAR(10),@TIMEIN,108) as datetime))
)

BEGIN
INSERT INTO SHORTLEAVE2
SELECT
@EID,
DateAdd(d, DateDiff(d, 0, Cast(CONVERT(VARCHAR(30),@Date,110) As datetime)), Cast(CONVERT(VARCHAR(10),@TIMEIN,108) as datetime)) AS CheckTime,
'I' AS CheckType,
@Remarks,
@Comments,
1
END


ELSE IF NOT EXISTS(SELECT * FROM SHORTLEAVE2
WHERE
EID = @EID
AND
CheckTime = DateAdd(d, DateDiff(d, 0, Cast(CONVERT(VARCHAR(30),@Date,110) As datetime)), Cast(CONVERT(VARCHAR(10),@TimeOut,108) as datetime))
)

BEGIN
SELECT
@EID,
DateAdd(d, DateDiff(d, 0, Cast(CONVERT(VARCHAR(30),@Date,110) As datetime)), Cast(CONVERT(VARCHAR(10),@TimeOut,108) as datetime)) AS CheckTime,
'O' AS CheckType,
@Remarks,
@Comments,
1
END

END

Thanks in Advance....

James K
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 01/14/2013 :  02:55:50  Show Profile  Reply with Quote
The way you have written the code, if the first condition is true, then the first update will be executed and, if it is false, then (and only then) will the second condition will be evaluated. Is that not what you want? If you want to examine each condition independent of the outcome of the previous condition, remove the ELSE clause(s).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/14/2013 :  03:16:48  Show Profile  Reply with Quote
why are you casting to varchar for date comparison? does CheckTime field store time part?

see this

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000