Solved

SQL 2005 IF EXISTS Not working

Posted on 2011-09-29
3
189 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 250 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 250 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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