simple sql - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

simple sql

Author

Topic

ssingh
Starting Member

25 Posts

Posted - 2002-02-15 : 09:28:51

Please let me know how do we make this work?

Declare @var int
set @var = 5
if @var > 3
begin
Select * into #temp_tbl from ABC_tbl where colA >= 150
end
else
begin

Select * into #temp_tbl from ABC_tbl where colA < 150
end

This gives me error:
There is already an object named '#temp_tbl' in the database. Can we not repeat the name of the table in the else clause, since either of if/else would be executed.
thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-02-15 : 11:32:45

have a look at re-working the select statements using the CASE conscript.

then you should be able to reduce the need for the IF.. ...ELSE...

for tests as simple as this it should work out neater, with just 1 SELECT statement.

lfmn
Posting Yak Master

141 Posts

Posted - 2002-02-15 : 14:32:05

This should fix your problem. If you are running this from the web, you will probably want to generate a tablename using random numbers.

It would be helpful if you said what your final goal for this query was. There is probably a better way to accomplish your goal.

I moved this to the pubs database in order to test it.

Declare @var int
declare @sql varchar(100)
declare @tablename varchar(20)
set @tablename = 'tbl_tmp'

set @sql = 'if exists (select * from sysobjects where name = ''' + @tablename + ''') begin drop table ' + @tablename + ' end'
execute(@sql)
set @var = 5
if @var > 3
begin
set @sql = 'Select * into ' + @tablename + ' from titles where price >= 10'
execute(@sql)
end
else
begin
set @sql = 'Select * into ' + @tablename + ' from titles where price < 10'
execute(@sql)
end

set @sql = 'select * from ' + @tablename
execute (@sql)

cursors are like hammers - sometimes you have to use them, but watch your thumb!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources