EXEC() results to populate cursor

Question

EXEC() results to populate cursor

kevvie.fowler

SSC Veteran

Points: 292
More actions
July 30, 2008 at 11:28 am

#210608

Within SQL Server I am trying to populate a cursor with no luck. After declaring a cursor you populate it with the results of a SELECT statement. The issue I am running into is that it appears that you cannot use the EXEC() function to populate the Cursor (it returns an error for me) and therefore I do not know how to concatenate a variable name with column names within the SELCECT statement to populate the cursor. The SELECT statement I am trying to use to populate the cursor is as follows (@dbname is the name of the database the cursor is running within and various db names will be passed into it through another cursor).
SELECT @dbname, PagePID, PageFID, OBJECTID FROM #DataPages WHERE [PAGETYPE] = 1
I'm not sure if anyone has come across this but any help would be greatly appreciated
Thanks!
April 14, 2023 at 3:40 am

This was removed by the editor as SPAM
April 14, 2023 at 3:44 am

This was removed by the editor as SPAM
April 17, 2023 at 1:36 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I'm not sure what you are trying to capture for data but if you google sp_MSforeachdb you may find out you can use this undocumented system procedure to accomplish your task. I don't know of anyway to declare a cursor and assign the select statement using dynamic sql.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Populate a temp table with the results of your dynamic SQL select statement, and then open the cursor on a select from the temp table.

kevvie.fowler SSC Veteran Points: 292 More actions · Answer 3

Jack - I was hoping to stay away from the SP but I may have no other choice.

Thanks for the reply.

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 4

Is this what you want??

DECLARE @dbname varchar(255),@query varchar(255)

DECLARE dbname CURSOR FOR

select name from master..sysdatabases where dbid > 6

OPEN dbname

FETCH NEXT FROM dbname INTO @dbname

WHILE(@@FETCH_STATUS = 0)

BEGIN

SET @query = 'SELECT '+@dbname+', PagePID, PageFID, OBJECTID FROM #DataPages WHERE [PAGETYPE] = 1'

exec (@query)

FETCH NEXT FROM dbname INTO @dbname

END

CLOSE dbname

DEALLOCATE dbname

"-=Still Learning=-"

Lester Policarpio

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 5

I accomplished this task by declaring synonyms

there is a "master" application database which has a table containing all the other databases

the cursor creates, using dynamic SQL, synonyms for every table needed in the procedure

for each database

it then invokes a stored procedure which references those synonyms

and so forth

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

With the advent of VARCHAR(MAX), there's just no need for cursors anymore... for example...

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL+CHAR(10)+' UNION ALL --------------------'+CHAR(10),'')

+REPLACE(

' SELECT '' '' AS DbName,*

FROM [ ].sys.SysObjects

WHERE OBJECTPROPERTY(ID,''IsMsShipped'') = 0'

,' ',Name)

FROM Master.sys.SysDataBases

WHERE DBID > 4

AND Name NOT LIKE 'ReportServer$%'

PRINT @SQL

EXEC (@SQL)

--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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Jeff Moden (8/1/2008)
With the advent of VARCHAR(MAX), there's just no need for cursors anymore... for example...
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL+CHAR(10)+' UNION ALL --------------------'+CHAR(10),'')
+REPLACE(
' SELECT '' '' AS DbName,*
FROM [ ].sys.SysObjects
WHERE OBJECTPROPERTY(ID,''IsMsShipped'') = 0'
,' ',Name)
FROM Master.sys.SysDataBases
WHERE DBID > 4
AND Name NOT LIKE 'ReportServer$%'
PRINT @SQL
EXEC (@SQL)

I believe there is still a limit of 256 tables in a query (haven't verified that, so I could be wrong), so if you have a lot of databases, you could run into a problem.

Of course, you could modify that code to do individual inserts for each database into a temp table.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Michael Valentine Jones (8/5/2008)
I believe there is still a limit of 256 tables in a query (haven't verified that, so I could be wrong), so if you have a lot of databases, you could run into a problem.
Of course, you could modify that code to do individual inserts for each database into a temp table.

Yeah... I keep forgetting about that limit. Thanks for the reminder... and you're absolutely correct/good idea... code could be easily modified to do individual inserts.

--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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

Jeff Moden (8/5/2008)
Michael Valentine Jones (8/5/2008)
I believe there is still a limit of 256 tables in a query (haven't verified that, so I could be wrong), so if you have a lot of databases, you could run into a problem.
Of course, you could modify that code to do individual inserts for each database into a temp table.
Yeah... I keep forgetting about that limit. Thanks for the reminder... and you're absolutely correct/good idea... code could be easily modified to do individual inserts.

I was just picking a few nits, but I sometimes regret showing people things like that.

A few weeks ago someone was getting some really weird errors when a query they wrote with 8000 unions in it failed. I think they overflowed some internal SQL Server counter.

wyattwong Newbie Points: 5 More actions · Answer 10

kevvie.fowler wrote:

Within SQL Server I am trying to populate a cursor with no luck. After declaring a cursor you populate it with the results of a SELECT statement. The issue I am running into is that it appears that you cannot use the EXEC() function to populate the Cursor (it returns an error for me) and therefore I do not know how to concatenate a variable name with column names within the SELCECT statement to populate the cursor. The SELECT statement I am trying to use to populate the cursor is as follows (@dbname is the name of the database the cursor is running within and various db names will be passed into it through another cursor). SELECT @dbname, PagePID, PageFID, OBJECTID FROM #DataPages WHERE [PAGETYPE] = 1 I'm not sure if anyone has come across this but any help would be greatly appreciated Thanks!

I have the same issue as you. I want to EXEC a stored procedure and use the returned result set in a cursor for further processing. But as you mentioned DECLARE CURSOR syntax does NOT allow user to populate the cursor with the result set returned from EXEC statement and it only works when the user provide a SELECT statement after "FOR"

DECLARE CURSOR cursor_name CURSOR FOR select_statement

pietlinden SSC Guru Points: 63503 More actions · Answer 11

So create a temporary table and insert the records there, and then create the cursor based on that?

(I guess ideally you'd create the temporary objects inside your stored procedure, and then they'd go out of scope once the stored procedure finished processing).

INSERT INTO #MyTempTable

EXEC Db.Schema.StoredProc @Param1='A';