Cursor - Using Variable in Query

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

Transact-SQL (2008)

Cursor - Using Variable in Query

Author

Topic

saketg
Starting Member

8 Posts

Posted - 2012-01-09 : 15:01:27

Hi,

I have a nested cursor. The outer cursor is iterating through all Columns in a table. Then I use the column name fetched from cursor and stored in a variable to build query for the inner cursor.

SQL Server works for:
SELECT COUNT(@myColumnName) FROM myTable;

But fails to work for:
SELECT COUNT(DISTINCT @myColumnName) FROM myTable;

It always returns answer as 1. Cursor is getting all Column names because I am printing those. Moreover, if I copy the query from output window and run, it gives correct output.

This issue exists for int and bit data types.

Similarly, the query below doesn't work:
SELECT COUNT(@myColumnName) FROM myTable WHERE @myColumnName = 1;

Am I missing something? Please help.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-09 : 15:13:34

You need to use dynamic SQL here as it isn't at all doing what you want. You have to build your query string and then use EXEC or sp_executesql to run it.

Here's a quick example:

declare @sql varchar(500), @t varchar(10), @c varchar(10)

select @t = 'table1', @c = 'column1'

set @sql = 'select * from ' + @t + ' where ' + @c + ' = 1'

--print @sql
exec(@sql)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 15:20:06

The outer cursor?

Post the cursors that you have already

What are you going to do with the cursor results? Updates, inserts, deletes? Reports to a temp table?

What?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

saketg
Starting Member

8 Posts

Posted - 2012-01-09 : 16:49:15

Hi Tara,

Thank you very much. Now the query executes correctly. Though, is there a way to save the output of this to a variable?

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 17:02:39

Care to answer my questions?

And post the code you have?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

saketg
Starting Member

8 Posts

Posted - 2012-01-09 : 17:11:04

quote:
Originally posted by X002548

The outer cursor?

Post the cursors that you have already

What are you going to do with the cursor results? Updates, inserts, deletes? Reports to a temp table?

What?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Hi Brett,

My code is:

USE CorrectDB

DECLARE @myID INTEGER;
DECLARE @myColumnName VARCHAR(250);
DECLARE @myDataType VARCHAR(250);
DECLARE @myResult INTEGER;
DECLARE @myPrint VARCHAR(250);

DECLARE cMasterTable CURSOR FOR
SELECT ID
FROM tblMaster
ORDER BY ID;

OPEN cMasterTable
FETCH NEXT FROM cMasterTable INTO @myID;

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cAllColumns CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tblDetails'
ORDER BY ORDINAL_POSITION;

OPEN cAllColumns
FETCH NEXT FROM cAllColumns INTO @myColumnName, @myDataType;

WHILE @@FETCH_STATUS = 0
BEGIN
IF @myDataType = 'int' OR @myDataType = 'nvarchar'
BEGIN
DECLARE cDetailsTable1 CURSOR FOR
SELECT COUNT(DISTINCT @myColumnName) FROM tblDetails
WHERE ID = @myID AND @myColumnName IS NOT NULL;

OPEN cDetailsTable1
FETCH NEXT FROM cDetailsTable1 INTO @myResult;

IF @myResult > 0
BEGIN
SELECT @myPrint = STR(@myID) + ' - ' + @myColumnName + ' - ' + STR(@myResult);
PRINT @myPrint;
END;
CLOSE cDetailsTable1;
DEALLOCATE cDetailsTable1;
END;
ELSE IF @myDataType = 'bit'
BEGIN
DECLARE cDetailsTable2 CURSOR FOR
SELECT COUNT(DISTINCT @myColumnName) FROM tblDetails
WHERE ID = @myID;

OPEN cDetailsTable2
FETCH NEXT FROM cDetailsTable2 INTO @myResult;

IF @myResult > 1
BEGIN
SELECT @myPrint = STR(@myID) + ' - ' + @myColumnName + ' - ' + STR(@myResult);
PRINT @myPrint;
END;
CLOSE cDetailsTable2;
DEALLOCATE cDetailsTable2;
END;

FETCH NEXT FROM cAllColumns INTO @myColumnName, @myDataType;
END;

CLOSE cAllColumns;
DEALLOCATE cAllColumns;

FETCH NEXT FROM cMasterTable INTO @myID;
END;

CLOSE cMasterTable;
DEALLOCATE cMasterTable;

Basically I have a huge table having 150+ columns. One of the fields is linked to a master table. I have to find out which columns have values for at least one row for every entry in Master table. I am trying to run the outer cursor for all entries in Master table. Second cursor on all columns present and then the inner conditions that are failing are just checking number of distinct values as per data type.

Based on Tara's suggestion, when I update cDetailsTable1 to dynamic sql then I get results but I have to check the condition also.

Hope this help.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-09 : 17:14:15

quote:
Originally posted by saketg

Hi Tara,

Thank you very much. Now the query executes correctly. Though, is there a way to save the output of this to a variable?

Thanks.

Yes, you can use sp_executesql for that: http://msdn.microsoft.com/en-us/library/ms188001.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

saketg
Starting Member

8 Posts

Posted - 2012-01-09 : 19:43:59

quote:
Originally posted by tkizer

quote:
Originally posted by saketg

Hi Tara,

Thank you very much. Now the query executes correctly. Though, is there a way to save the output of this to a variable?

Thanks.

Yes, you can use sp_executesql for that: http://msdn.microsoft.com/en-us/library/ms188001.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hi Tara,

I tried that earlier but could not make it work. After your post I tried again and could make it work. Thanks a lot.

With regards,
Saket

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-09 : 19:51:12

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources