Problem SELECTing declared variables

Question

Problem SELECTing declared variables

daveangel

SSC Enthusiast

Points: 116
More actions
December 2, 2005 at 6:44 am

#90041

Hi Folks,
I have a table called base1005 (word 'base' and mmyy) in my database and i am trying to count the number of records in each file (as well as some other commands) from this file. I want to call the file name through a variable however the SELECT part of the following program states that the variable @churn1 has not been declared. Does anyone know why this is???
declare @churn varchar(20),@churn1 varchar(20)
set @churn='1005'
set @churn1='base'+@churn
print @churn1
select * from @churn1
BTW - the PRINT statement works but the SELECT part doesn't!
Cheers

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

You have to use dynamic sql for this....

declare @churn varchar(20),@churn1 varchar(20)
set @churn='1005'
set @churn1='base'+@churn
print @churn1
--select * from @churn1 - INCORRECT
exec('select * from ' + @churn1)

read this wonderful article on dynamic sql!

**ASCII stupid question, get a stupid ANSI !!!**

daveangel SSC Enthusiast Points: 116 More actions · Answer 2

Thank you very very very much!!

I just thought I would reply to say thanks before I dive into the scary topic of Dynamic SQL.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

Do I miss something here Friday.

Wouldn't this work!

declare @churn varchar(20),@churn1 varchar(20)

set @churn='1005'

set @churn1='base'+@churn

print @churn1

--select * from @churn1

select @churn1 churn1

Regards,
gova

daveangel SSC Enthusiast Points: 116 More actions · Answer 4

I would still need a dynamic query to SELECT * from the base1005 table though wouldn't I?

I am a bit confused.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

My Mistake.

Friday Morning. I didn't realise @churn1 holds a table name. Sushila is right. Dynamic SQL is the answer.

Print statement works and select didn't misled me.

Regards,
gova

daveangel SSC Enthusiast Points: 116 More actions · Answer 6

No probs.

Thanks for your help anyway.

Hope friday afternoon is a bit better.

Ian Yates SSCoach Points: 19738 More actions · Answer 7

Govinn, I think you might have misread the question. The variable stores the table name

Ian Yates SSCoach Points: 19738 More actions · Answer 8

Whoops - seems you and I are often on here replying to questions at the same time Govinn - seems you corrected your misread while I was typing!

Apologies