If Exists - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

If Exists

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 04:25:46

Hi,


IF EXISTS
(
 SELECT * FROM dbo.mepActivity_tbl  
 WHERE  Primaryid=1 and wstatus = 'Y'  
)
BEGIN
UPDATE dbo.mepActivity_tbl 
SET COL_NAME='N' WHERE  Primaryid=1 and wstatus = 'Y'
SELECT COL1,COL2,COL3.........FROM TBL
END
ELSE
BEGIN
SELECT COL1,COL2,COL3.........FROM TBL
END

Could you please suggest me the best way for the above one?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-01 : 04:29:22

It increase some level of performance

IF EXISTS
(
SELECT 'x' FROM dbo.mepActivity_tbl
WHERE Primaryid=1 and wstatus = 'Y'
)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 04:37:10

quote:
Originally posted by senthil_nagore

It increase some level of performance

IF EXISTS
(
SELECT 'x' FROM dbo.mepActivity_tbl
WHERE Primaryid=1 and wstatus = 'Y'
)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Sorry.
See actually,i will tell you the exact scnerio.hope it will make clear.
In my proc,
1.
UPDATE dbo. Tbl
SET wstatus ='N'
WHERE enddate< DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) and wstatus = 'Y'
2. SELECT clo1,,......from dbo.Tbl where wstatus = 'Y' and status = 'Y' AND startdate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND startdate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

this two steps has to be done in my procedure..
here startdate,enddate is datetime column.

actually,everytime when i run the sp,first updates and Fetching the record.i felt,it is the dirty way.

so please

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 04:41:36

IF EXISTS
(
SELECT * FROM dbo.mepActivity_tbl
WHERE Primaryid=1 and wstatus = 'Y'
)
BEGIN
UPDATE dbo.mepActivity_tbl
SET COL_NAME='N' WHERE Primaryid=1 and wstatus = 'Y'
END
SELECT COL1,COL2,COL3.........FROM TBL

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 04:44:34

quote:
Originally posted by madhivanan

IF EXISTS
(
SELECT * FROM dbo.mepActivity_tbl
WHERE Primaryid=1 and wstatus = 'Y'
)
BEGIN
UPDATE dbo.mepActivity_tbl
SET COL_NAME='N' WHERE Primaryid=1 and wstatus = 'Y'
END
SELECT COL1,COL2,COL3.........FROM TBL

Madhivanan

Failing to plan is Planning to fail

Iam sorry madhi.i have edited my post above.please read above and tell me

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 04:48:17

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

IF EXISTS
(
SELECT * FROM dbo.mepActivity_tbl
WHERE Primaryid=1 and wstatus = 'Y'
)
BEGIN
UPDATE dbo.mepActivity_tbl
SET COL_NAME='N' WHERE Primaryid=1 and wstatus = 'Y'
END
SELECT COL1,COL2,COL3.........FROM TBL

Madhivanan

Failing to plan is Planning to fail

Iam sorry madhi.i have edited my post above.please read above and tell me

If records exist for Primaryid=1 and wstatus = 'Y' , you want to update the table and select data from the table. Right?

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 04:54:02

quote:
Originally posted by madhivanan

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

IF EXISTS
(
SELECT * FROM dbo.mepActivity_tbl
WHERE Primaryid=1 and wstatus = 'Y'
)
BEGIN
UPDATE dbo.mepActivity_tbl
SET COL_NAME='N' WHERE Primaryid=1 and wstatus = 'Y'
END
SELECT COL1,COL2,COL3.........FROM TBL

Madhivanan

Failing to plan is Planning to fail

Iam sorry madhi.i have edited my post above.please read above and tell me

If records exist for Primaryid=1 and wstatus = 'Y' , you want to update the table and select data from the table. Right?

Madhivanan

Failing to plan is Planning to fail

sorry again madhi.i will tell you now
Sorry.
See actually,i will tell you the exact scnerio.hope it will make clear.
In my proc,
1.
UPDATE dbo. Tbl
SET wstatus ='N'
WHERE enddate< DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) and wstatus = 'Y'
2. SELECT clo1,,......from dbo.Tbl where wstatus = 'Y' and status = 'Y' AND startdate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND startdate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

this two steps has to be done in my procedure..
here startdate,enddate is datetime column.

actually,everytime when i run the sp,first updates and Fetching the record.i felt,it is the dirty way.means,before updating,it has to check the records,if it is updated already,then just select..
if not, update and select..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 05:03:41

See my first reply. You need to add the dates conditions in all places
It does what you want

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 05:08:58

quote:
Originally posted by madhivanan

IF EXISTS
(
SELECT * FROM dbo.mepActivity_tbl
WHERE Primaryid=1 and wstatus = 'Y'
)
BEGIN
UPDATE dbo.mepActivity_tbl
SET COL_NAME='N' WHERE Primaryid=1 and wstatus = 'Y'
END
SELECT COL1,COL2,COL3.........FROM TBL

Madhivanan

Failing to plan is Planning to fail

You mean above..Okay..Let me try now immediately and will say..
i have one more doubt in the date field still..
original Posted earlier.
where startdate >= dateadd(day, datediff(day, 0, getdate()), 0)
and startdate < dateadd(day, datediff(day, 0, getdate()), 1)

y it can't be by this..
startdate <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 05:13:37

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

IF EXISTS
(
SELECT * FROM dbo.mepActivity_tbl
WHERE Primaryid=1 and wstatus = 'Y'
)
BEGIN
UPDATE dbo.mepActivity_tbl
SET COL_NAME='N' WHERE Primaryid=1 and wstatus = 'Y'
END
SELECT COL1,COL2,COL3.........FROM TBL

Madhivanan

Failing to plan is Planning to fail

You mean above..Okay..Let me try now immediately and will say..
i have one more doubt in the date field still..
original Posted earlier.
where startdate >= dateadd(day, datediff(day, 0, getdate()), 0)
and startdate < dateadd(day, datediff(day, 0, getdate()), 1)

y it can't be by this..
startdate <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

where startdate >= dateadd(day, datediff(day, 0, getdate()), 0)
and startdate < dateadd(day, datediff(day, 0, getdate()), 1)

will filter today's data only

whereas

startdate <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

filter all data equal or prior to today's date

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 05:25:35

quote:
Originally posted by madhivanan

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

IF EXISTS
(
SELECT * FROM dbo.mepActivity_tbl
WHERE Primaryid=1 and wstatus = 'Y'
)
BEGIN
UPDATE dbo.mepActivity_tbl
SET COL_NAME='N' WHERE Primaryid=1 and wstatus = 'Y'
END
SELECT COL1,COL2,COL3.........FROM TBL

Madhivanan

Failing to plan is Planning to fail

You mean above..Okay..Let me try now immediately and will say..
i have one more doubt in the date field still..
original Posted earlier.
where startdate >= dateadd(day, datediff(day, 0, getdate()), 0)
and startdate < dateadd(day, datediff(day, 0, getdate()), 1)

y it can't be by this..
startdate <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

where startdate >= dateadd(day, datediff(day, 0, getdate()), 0)
and startdate < dateadd(day, datediff(day, 0, getdate()), 1)

will filter today's data only

whereas

startdate <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

filter all data equal or prior to today's date

Madhivanan

Failing to plan is Planning to fail

Thank you very mcuh for your explanation and really good.
still i have confusion,filter today's date which one i can go ahead with?
approach 1 or 2. please

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 05:26:44

Approach 1

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 05:29:19

quote:
Originally posted by madhivanan

Approach 1

Madhivanan

Failing to plan is Planning to fail

Thank you very much again madhi.you are great..your fastest response,really very good.
Its working Ok(As u suggested the if exists begin update end select)superb!..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 05:30:55

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

Approach 1

Madhivanan

Failing to plan is Planning to fail

Thank you very much again madhi.you are great..your fastest response,really very good.
Its working Ok(As u suggested the if exists begin update end select)superb!..

You are welcome

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-01 : 14:32:53

Since you are using SQL 2008 you could take advantage of the OUTPUT clause.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-02 : 02:12:19

quote:
Originally posted by Lamprey

Since you are using SQL 2008 you could take advantage of the OUTPUT clause.

Oh!..Thanks...yeah.Iam using sql server 2008..
Could you show me some sample code of using OUTPUT Clause for this sceneario? please...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-03 : 11:31:17

Sure, basically you need to set up a temp table or table variable and then insert into that using the OUTPUT..INTO clause. Here is a simple example:

-- Setup Sample Data
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), Val VARCHAR(50), IsEnabled BIT)

INSERT @MyTable
SELECT 'Val1', 0
UNION ALL SELECT 'Val2', 1


-- Create Table Variable for OUTPUT clause
DECLARE @MyOut TABLE(ID INT, Val VARCHAR(50))

-- Run Upadte
UPDATE
	@MyTable
SET
	IsEnabled = 1
OUTPUT
	Inserted.ID, 
	inserted.Val
INTO
	@MyOut
WHERE
	IsEnabled = 0

-- Display OUTPUTed data	
SELECT *
FROM @MyOut

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 12:01:57

only think to be aware of is that you can't use the OUTPUT clause if any of the tables involved have TRIGGERS. Probably isn't a problem for you but worth knowing..

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 01:17:24

Thanks lampery and charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources