CURSOR Select clause

I need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error:

Server: Msg 16924, Level 16, State 1, Line 78
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @.sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor?

select colnum, coldesc, colname into #ae_defs from ae_adefs
select @.Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') +
', ' +
(select colname from #ae_defs where coldesc = 'PATIENT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT DATE') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT TYPE') +
', DocID from ae_dtl1'
DECLARE myCursor CURSOR FOR
Select @.SQL

OPEN myCursor
print @.@.Cursor_rows
FETCH NEXT FROM myCursor into @.var1, @.var2, @.var3, @.var4, @.var5, @.DocID

No, what you have is one value. Select @.SQL The value is the contents of @.SQL is just a single scalar value, not a query (even if the contents of the @.SQL variable happens to be SQL. It could be the declaration of independence :)

Check this thread for how to do this using a GLOBAL cursor, you can't do it with a LOCAL one dynamically like this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=742503&SiteID=1

|||

I have modified according to 1 of the 2 examples in that thread. One of the examples still shows a fixed table.field in the select clause. I need to use a variable in the select clause.

The modified code is further down. The immediate results follow and when using this syntax it now cant seem to see my table, which is present as dbo owned, as well as does not recognize the cursor when trying to open.

Thank you so much!

DECLARE myCursor CURSOR GLOBAL FOR SELECT field3, field2, field1, field4, field5, DocID from ae_dtl1 (the result of @.SQL)

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'ae_dtl1'.

Server: Msg 16916, Level 16, State 1, Line 44
A cursor with the name 'myCursor' does not exist.

The code (well the essential parts...):

select colnum, coldesc, colname into #ae_defs5 from ae_adefs
select @.Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') +
', ' +
(select colname from #ae_defs where coldesc = 'PATIENT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT DATE') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT TYPE') +
', DocID from ae_dtl1'

set @.SQL = 'DECLARE myCursor CURSOR GLOBAL FOR SELECT ' + @.SQL
exec sp_executesql @.sql

OPEN myCursor
print @.@.Cursor_rows
FETCH NEXT FROM myCursor into @.var1, @.var2, @.var3, @.var4, @.var5, @.DocID