asked on

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

ASKER CERTIFIED SOLUTION

junkymail1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dan_mason

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?

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial