Problems with a conditional statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problems with a conditional statement

Author

Topic

virtuesoft
Starting Member

8 Posts

Posted - 2009-12-23 : 13:06:37

I'm having trouble with the following IF... ELSE statement.

I would have thought that in the script below only the first IF section should be executed because the @LegalEntityGroup is not null. However, this is not the case. It seems that all three IF sections are executed. I get the following error message...

Msg 2714, Level 16, State 1, Line 28
There is already an object named '#LegalEntities' in the database.
Msg 2714, Level 16, State 1, Line 35
There is already an object named '#LegalEntities' in the database.

DECLARE @LegalEntityGroup varchar(4000)
DECLARE @LegalEntity varchar(4000)

SET @LegalEntityGroup = 'All HF Accounts'
SET @LegalEntity = NULL

-- Populate a table listing all the legal entities to search for
IF object_id('tempdb..#LegalEntities') IS NOT NULL
	DROP TABLE #LegalEntities

IF @LegalEntityGroup IS NOT NULL
	BEGIN
		SELECT	LegalEntity
		INTO	#LegalEntities
		FROM	[config].[LegalEntityGroupMember]
		WHERE	[Group] IN (SELECT * FROM config.fnSplit(@LegalEntityGroup, ','))
	END
ELSE 
	BEGIN
		IF @LegalEntity IS NOT NULL
			BEGIN
				SELECT	LegalEntity
				INTO	#LegalEntities
				FROM	[config].[LegalEntity]
				WHERE	LegalEntity IN (SELECT * FROM config.fnSplit(@LegalEntity, ','))
			END
		ELSE
			BEGIN
				SELECT	LegalEntity
				INTO	#LegalEntities
				FROM	config.fnUserLegalEntities(1,NULL)
			END
	END

Can anyone help me out with this?

mfemenel
Professor Frink

1421 Posts

Posted - 2009-12-23 : 13:44:38

When I have things like this I take the "working part" of the code out and then put in select statements. So take out your insert into # lines and in your begin/end blocks put 'select statement 1', 'select statement 2' etc. That will show you exactly what you're hitting.
It wouldn't mean all 3 are firing, my guess is you have a combination of 2 that are firing. From there you can evaluate your statements as to why that's happening.

Also, did you try explicitly dropping the temp table and then running your code?

Mike
"oh, that monkey is going to pay"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-23 : 13:50:28

You need to use CREATE TABLE for #LegalEntities instead of SELECT INTO since you are trying to create it more than one in your code, hence the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

virtuesoft
Starting Member

8 Posts

Posted - 2009-12-24 : 03:37:01

Thanks very much guys! I've managed to get the code working by creating the table first.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources