Must Declare the Table Variable

Question

Must Declare the Table Variable

mj_saran2002

SSC-Addicted

Points: 406
More actions
November 17, 2009 at 2:23 pm

#99960

HI All
am trying to create a cursor,
which will fetch the count of a particular record from different database, in that am getting the following error, must declare the table variable @dbname
please see the code below
declare @dbname varchar(20)
declare E_Count Cursor
for
select name from sys.databases
where name like '%_Voters'
open E_Count
fetch next from E_Count
into @dbname
while @@FETCH_STATUS = 0
begin
insert into #count(T_Count,dbname)
select COUNT(*) as T_Count,@dbname from @dbname +'.'+Prod_Stats
Where ethinicity = 'U1'
fetch next from E_count
End
close E_Count
am getting error in select statement after insert into from @dbname (must Declare the table variable)
please help me with this

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What you are attempting to do requires the use of dynamic sql. Please take the time to read about dynamic sql in BOL (Books Online, the SQL Server Help System). It will give you the necessary background and if you still have questions (and you could, as BOL isn't always understandable), ask more questions.

shaikh.kurshid Old Hand Points: 301 More actions · Answer 2

Hi,

declare @table table

select * into @table

from abc

Am i need to give all columns in table variable or else can i do directly copying from table1 to table variable.......In table1 their 22 columns .....

so please suggest the code...

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

shaikh.kurshid (2/11/2011)
Hi,
declare @table table
select * into @table
from abc
Am i need to give all columns in table variable or else can i do directly copying from table1 to table variable.......In table1 their 22 columns .....
so please suggest the code...

You cannot do a SELECT/INTO into a Table Variable in SQL Server 2005. The variable declaration needs to have the column names and datatypes as if it were a CREATE TABLE statement.

After that, you can use INSERT/SELECT to populate the table variable. You don't need to list the columns if they match, but I treat it as a best practice to do so.

So far as having 22 columns, heh... so what? You don't have to type them. Open the object browser in SSMS, drill down to the table, click and drag the whole COLUMNS section into your code. I go one step further... I drop the column names into WORD and replace the ", " with "," and a carriage return mark. Then I paste THAT into SSMS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)