SQL 2005 IF EXISTS Not working

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

smares32371Asked:
Who is Participating?
 
junkymail1Connect With a Mentor Commented:
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
 
dan_masonCommented:
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
 
Anthony PerkinsConnect With a Mentor Commented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.