Cursor variable not declared issue

29 Sep 2005 5:09 PM

SJM

Ok, I missed the boat somewhere.

I prepared the query below to go though all the columns and tables in
the database and return the count of distinct values for each column and
label them with the table and column names

When I run the query, I receive the following error from the part
labeled #1:
Server: Msg 137, Level 15, State 2, Line 44
Must declare the variable '@tbl_name'.

What I don’t understand is why this error is occurring. I defined the
variable and populated it. I commented out part #1 and tried PRINT
@tbl_name @col_name which returned appropriate values.

I have a workaround of commenting out the part labeled #1 and instead
insert the following which produced a list of queries that I copied and
pasted into a new QA window and executed.
PRINT 'select count(distinct ' + @col_name + ') ' + '"' + @tbl_name +
'.' + @col_name + '"' + ' from ' + @tbl_name

I don’t understand why I cannot substitute the variables in a query as I
wish to.

I am also considering building a command string and using exec
sp_executesql.

I welcome comments and suggestions on this matter.

-- -- --

DECLARE @tbl_name varchar(255), @col_name varchar(255)

DECLARE CURS_sys_tables_and_cols CURSOR FOR
select sysobjects.name, syscolumns.name from syscolumns, sysobjects
where sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
and sysobjects.name NOT like 'SYS%'
and sysobjects.name NOT IN ( LIST OF TABLES I DON’T WANT)
order by sysobjects.name

OPEN CURS_sys_tables_and_cols

FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @tbl_name, @col_name

WHILE @@FETCH_STATUS = 0
BEGIN

-- #1
PRINT @tbl_name + '.' + @col_name

Select count(distinct @col_name) from @tbl_name

PRINT '---'

FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @tbl_name, @col_name

END

CLOSE CURS_sys_tables_and_cols

DEALLOCATE CURS_sys_tables_and_cols

*** Sent via Developersdex http://www.developersdex.com ***

29 Sep 2005 5:48 PM

Jerry Spivey

SJM,

I think you'll need to use Dynamic SQL to use a variable for the table name
in your query i.e., EXEC or sp_executesql.

Check it out in the SQL BOL and at Erland's article:

http://www.sommarskog.se/dynamic_sql.html

HTH

Jerry
Show quote

"SJM" <nospam@devdex.com> wrote in message
news:eIAwXiRxFHA.624@TK2MSFTNGP11.phx.gbl...
>
> Ok, I missed the boat somewhere.
>
> I prepared the query below to go though all the columns and tables in
> the database and return the count of distinct values for each column and
> label them with the table and column names
>
> When I run the query, I receive the following error from the part
> labeled #1:
> Server: Msg 137, Level 15, State 2, Line 44
> Must declare the variable '@tbl_name'.
>
> What I don't understand is why this error is occurring. I defined the
> variable and populated it. I commented out part #1 and tried PRINT
> @tbl_name @col_name which returned appropriate values.
>
> I have a workaround of commenting out the part labeled #1 and instead
> insert the following which produced a list of queries that I copied and
> pasted into a new QA window and executed.
> PRINT 'select count(distinct ' + @col_name + ') ' + '"' + @tbl_name +
> '.' + @col_name + '"' + ' from ' + @tbl_name
>
> I don't understand why I cannot substitute the variables in a query as I
> wish to.
>
> I am also considering building a command string and using exec
> sp_executesql.
>
> I welcome comments and suggestions on this matter.
>
> -- -- --
>
> DECLARE @tbl_name varchar(255), @col_name varchar(255)
>
> DECLARE CURS_sys_tables_and_cols CURSOR FOR
> select sysobjects.name, syscolumns.name from syscolumns, sysobjects
> where sysobjects.id = syscolumns.id
> and (sysobjects.xtype='U' or sysobjects.xtype='S')
> and sysobjects.name NOT like 'SYS%'
> and sysobjects.name NOT IN ( LIST OF TABLES I DON'T WANT)
> order by sysobjects.name
>
> OPEN CURS_sys_tables_and_cols
>
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @tbl_name, @col_name
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
>
> -- #1
> PRINT @tbl_name + '.' + @col_name
>
> Select count(distinct @col_name) from @tbl_name
>
> PRINT '---'
>
>
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @tbl_name, @col_name
>
> END
>
> CLOSE CURS_sys_tables_and_cols
>
> DEALLOCATE CURS_sys_tables_and_cols
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

29 Sep 2005 8:00 PM

SJM

Indeed, I thought I might need to build strings and use sp_executesql.
Thanks for the pointer to the article, I missed it in my google
searches.

*** Sent via Developersdex http://www.developersdex.com ***