If condition not working inside dynamic sql

Question

In below dynamic sql I am trying to check whether temporary table (#Table) already exists if not then create using some other table.

But my problem is that irrespective what is the value in IF condtion, I am always getting error for select statement which is inside If condition

There is already an object named '#Table' in the database.

Below is the sql code.

declare @sqlstring varchar(max)

set @sqlstring=N'

select * into #Table from mst_country


if(object_id(''tempdb..#Table'') is null)
begin

    select * into #Table from mst_country_bkp

end'

exec(@sqlstring)

Can any one please let us know why it can be happening?

Actually my original query is much complex and bigger..It is just a small snippet to show what is the exact problem.. If you replace mst_country whith any of your database table it will give you error..so basically to simplify question I have used this query. — Sagar Shirke
– Sagar Shirke, Commented Oct 28, 2015 at 9:17

Rahul Tripathi · Accepted Answer · 2015-10-28 09:44:19Z

3

You need to drop the temp table once you are done with your query like this:

declare @sqlstring varchar(max)

set @sqlstring=N'
IF OBJECT_ID(''tempdb.dbo.#Table'', ''U'') IS NOT NULL
DROP TABLE #Table;  

select * into #Table from mst_country


if(object_id(''tempdb..#Table'') is null)
begin  
    select * into #Table from mst_country_bkp    
end'

exec(@sqlstring)

EDIT:

The issue is that you cannot have two SELECT INTO inside the same sql statement. This is a leftover from SQL 6.5 which did not have deferred name resolution. Try this:

declare @sqlstring varchar(max)

set @sqlstring=N'
IF OBJECT_ID(''tempdb.dbo.#Table'', ''U'') IS NOT NULL
DROP TABLE #Table;  

select * into #Table from mst_country

insert into #Table(col1,col2,....)
select col1,col2,.... from mst_country_bkp    
end'

exec(@sqlstring)

edited Oct 28, 2015 at 9:44

answered Oct 28, 2015 at 9:13

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

DavidG Over a year ago

Perhaps put an if exists...drop table at the start.

DavidG Over a year ago

@SagarShirke At least try the code given to you before dismissing it so quickly.

t-clausen.dk Over a year ago

With your last modification, you risk having data from both tables in the temporary table

Sagar Shirke Over a year ago

@RahulTripathi...Thanks, your link has helped me to understand what is the problem..I appreciate your time and efforts for helping me..

HaveNoDisplayName Over a year ago

Very Good @RahulTripathi +1. Nice article you found

|

t-clausen.dk · Accepted Answer · 2015-10-28 09:42:57Z

2

When compiling the sqlserver doesn't allow you creating the same temporary table in the same scope twice. You can actually cheat the sqlserver like this, so it doesn't realize that the temporary table was already created:

declare @sqlstring varchar(max)

set @sqlstring=N'

exec(''select * into #Table from mst_country'')


if(object_id(''tempdb..#Table'') is null)
    select * into #Table from mst_country_bkp
'

exec(@sqlstring)

answered Oct 28, 2015 at 9:42

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

2 Comments

Sagar Shirke Over a year ago

It worked perfectly fine..Thanks..But one query..You are saying " sqlserver doesn't allow you creating the same temporary table in the same scope twice" but I have used IF condition..so you want to say..sql server will not consider if statement?

t-clausen.dk Over a year ago

That is correct. You are attempting to create the same table again. Even though you have an condition. SQL server doesn't evaluate your condition, the error occurs at compile time, not the execution itself

Collectives™ on Stack Overflow

If condition not working inside dynamic sql

2 Answers 2

16 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related