Include local variable in cursor

[dpops]

Hi Gurus,

Can I get some help. I wanted to get the following code to return a result that using dml to include local variable both in cloumn side and table side. Sorry for the confusion. It is much easier to look at the code. Please...

JohnZ
858-5462023
johnz@missionfcu.org


Declare @tname varchar(25), @cname varchar(25)
Declare output_cursor CURSOR FOR
select tablename, columnname
from ref_sl where flag = 'Y'

Open output_cursor
Fetch Next from output_cursor INTO @tname, @cname
while @@fetch_status = 0
begin
--This is the line I want to use, but didn't work
--EXEC ('SELECT ' + @tname +', ' + @cname + ', count(' + @cname + ')' + ' FROM ' + @tname + ' group by ' + @cname)
Print 'Table Name: ' + @tname + ' Column Nmae: ' + @cname
EXEC ('SELECT COUNT(*) AS TotalCount FROM ' + @TNAME)
EXEC ('SELECT ' + @cname + ', count(' + @cname + ') as GroupCount' + ' FROM ' + @tname + ' group by ' + @cname)
fetch next from output_cursor INTO @tname, @cname
end

--select @@cursor_rows
close output_cursor
deallocate output_cursor

 

[tzamora]

You can create a temp table to include your @local_vars and then select from it.

http://programmer-by-the-hour.com

 

[tlbroadbent]

Use the following.

EXEC ('SELECT TblName=''' + @tname +''', ' + @cname + ', RowCnt=count(*)' + ' FROM ' + @tname + ' group by ' + @cname) If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[dpops]

This makes it work. Thanks.

Can you explain why use the '' + @tname +'', which is two single quotes back to back twice to delimit? If I use a constant instead of variable. Will this be same. Very appreciated.

John

 

[tlbroadbent]

If you remove the two single quotes, then you will encounter the same problem you had when you first posted. The name of the table will be treated as a column name and because it doesn't exist as a column in the table, the statement wil' errror. The two adjacent single quotes wil become 1 single quote in the SQL Statment. This will cause SQL Server to recognize the value as a literal rather than a column name. The statemnt will look like the following.

SELECT TblName='YourTableName', ColumnName, RowCnt=count(*) FROM YourTableName group by ColumnName

Without the single quotes it would look like this.

SELECT TblName=YourTableName, ColumnName, RowCnt=count(*) FROM YourTableName group by ColumnName
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[dpops]

Terry,

This is very thorough even better than MSDN support line. Thanks for all.

John