?
Solved

SQL 2005 IF EXISTS Not working

Posted on 2011-09-29
3
Medium Priority
?
199 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
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

588 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