?
Solved

SQL 2005 IF EXISTS Not working

Posted on 2011-09-29
3
Medium Priority
?
190 Views
Last Modified: 2012-08-14
In my sql procedure I'm trying to check if the record exist update it  else if dont exist set to null.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go











---Date Created:	May 06, 2011
---Created by:		Sal Mares
---Description:		


ALTER PROCEDURE [dbo].[proc_GetAD]
AS

    BEGIN TRANSACTION T1

		SELECT count(*) FROM Openquery 
		(abc,
		'
		SELECT 	* 
		FROM 	adFields
		WHERE Adid in 
			(
			SELECT 	distinct Adid
			FROM 	price p, plan pl
			WHERE	p.planid = pl.planid
			and 	Editionid = 100
			and 	RunDate >  DATEADD(DAY,-31,getdate() )
			)
		')

		IF @@ERROR != 0 --check @@ERROR variable after each DML statements..
			BEGIN
				RETURN
			END
		ELSE
			BEGIN
				TRUNCATE TABLE tAdWebFields
	
		 		IF @@ERROR != 0 --check @@ERROR variable after each DML statements..
					BEGIN
						RETURN
					END
				ELSE
					BEGIN
						INSERT INTO tAdWebFields 
						SELECT * FROM Openquery 
							(abc,
							'
							SELECT 	* 
							FROM 	adFields
							WHERE Adid in 
								(
								SELECT 	distinct Adid
								FROM 	price p, plan pl
								WHERE	p.planid = pl.planid
								and 	Editionid = 100
								and 	RunDate >  DATEADD(DAY,-31,getdate() )
								)
							')
						
						

						
					IF (EXISTS (SELECT * FROM tAdWebFields where WebFieldsId = 20 AND StringInfo > ''   ))
						BEGIN
							UPDATE	tAdvertiserAds
							SET	companyname = StringInfo
							FROM	tAdWebFields
							WHERE	tAdWebFields.WebFieldsId = 20 and	tAdWebFields.AdId = tAdvertiserAds.abcAdID

						END
					ELSE
						BEGIN
							UPDATE tAdvertiserAds
						    SET	companyname = ''
						    FROM	tAdWebFields
						    WHERE	tAdWebFields.WebFieldsId = 20 and	tAdWebFields.AdId = tAdvertiserAds.abcAdID
						    
                        END

							
						
						
					
					END

					


			END
	COMMIT TRANSACTION T1
	
	---Ads Date
	BEGIN TRANSACTION T2
	
		SELECT count(*) FROM Openquery 
		(abc,
		'SELECT DISTINCT adid, rundate 
		FROM 	price p, plan pl
		WHERE 	p.planid  = pl.planid
		AND 	editionid = 100
		AND	rundate > DATEADD(DAY,-31,getdate() )'
		)
	
		IF @@ERROR != 0 --check @@ERROR variable after each DML statements..
			BEGIN
				RETURN
			END
		ELSE
			BEGIN
			
				TRUNCATE TABLE tAdRunDate
			
		 		IF @@ERROR != 0 --check @@ERROR variable after each DML statements..
					BEGIN
						RETURN
					END
				ELSE
					BEGIN
						INSERT INTO tAdRunDate (abcAdId, RunDate)
						SELECT * FROM Openquery 
						(abc,
						'SELECT DISTINCT adid, rundate 
						FROM 	price p, plan pl
						WHERE 	p.planid  = pl.planid
						AND 	editionid = 100
						AND	rundate > DATEADD(DAY,-31,getdate() )'
						)
					END
			END
					
	COMMIT TRANSACTION T2

Open in new window

0
Comment
Question by:smares32371
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 2

Accepted Solution

by:
junkymail1 earned 1000 total points
ID: 36817205
On a cursory glance you seem to have the select statement correct.  It should be in the format:

if (Exists ( Select * FROM Table_ID where ID = 88 AND Product_Name > '' ))
      Begin
            select 1
      end
else
      begin
            select 0
      end

Which it seems to be.  What I usually do when this isn't working is just run the:
Select * FROM Table_ID where ID = 88 AND Product_Name > ''
part of my select to see if anything comes back.  If anything comes back the Exists should work fine, if nothing is coming back it should drop to the else.
0
 
LVL 6

Expert Comment

by:dan_mason
ID: 36902646
For efficiency, change that * for a 1:

IF EXISTS (SELECT 1 FROM tAdWebFields where WebFieldsId = 20 AND StringInfo > '' )

In general, you should be changing all the other *'s in your query to column names too.

Also, which part of the IF EXISTS isn't working? Both outcomes, or just one of them?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 36904804
>>For efficiency, change that * for a 1:<<
While I always use IF EXISTS (SELECT 1 ..., you will not find any differnces between the excution plan for either:
IF EXISTS (SELECT * FROM tAdWebFields where WebFieldsId = 20 AND StringInfo > '' )
Or
IF EXISTS (SELECT 1 FROM tAdWebFields where WebFieldsId = 20 AND StringInfo > '' )

The SQL Server engine is smart enough to know what you want.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question