Hi All
I have a very complicated query

Originally I insert the data in a temp table and use cursor to get the data from the temp table. Everything works fine.
Then I thought, I could just use the cursor and get the data directly from the query without the temp table. However, I am getting a syntax error.
I was wondering is there a resctrction on using the cursor?

The code below is my original stored procedure code (with temp table)

Code:
create table #Email(
   ClientUserId IDType,
   ClientEmail EmailType,
   ASNum int,
   ATNum int,
   Language char(1)
)

insert into #Email
select b.ClientUserID, b.ClientEmailAddress, sum(s1), sum(s2), b.Language
from (
   select AccountNum, sum(c1) as s1, sum(c2) as s2
   from (
      select AccountNum, count(1) c1, 0 c2 from AStatement
      where CreateDate < (getdate()) group by AccountNum
      union all
      select AccountNum, 0, count(1) from AATS
      where CreateDate < (getdate()) groupt by AccountNum
   ) v1
   group by AccountNum
) a, Client b, ClientAccount c, Doc d, ClientService e
where c.ClientUserId = b.ClientUserID
and c.ClientUserID = e.ClientUserID
and c.AccountNum = d.AccountNum
and c.AccountNum = a.AccountNum
and e.Services = "newDoc"
and d.Method in ('C','D')
group by b.ClientUserID

declare email_crsr cursor
for select ClientEmail, ASNum, ATNum, Language from #Email

open email_crsr

fetch email_crsr

I try to change to the following
Code:
declare email_crsr cursor for
select b.ClientUserID, b.ClientEmailAddress, sum(s1), sum(s2), b.Language
from (
   select AccountNum, sum(c1) as s1, sum(c2) as s2
   from (
      select AccountNum, count(1) c1, 0 c2 from AStatement
      where CreateDate < (getdate()) group by AccountNum
      union all
      select AccountNum, 0, count(1) from AATS
      where CreateDate < (getdate()) groupt by AccountNum
   ) v1
   group by AccountNum
) a, Client b, ClientAccount c, Doc d, ClientService e
where c.ClientUserId = b.ClientUserID
and c.ClientUserID = e.ClientUserID
and c.AccountNum = d.AccountNum
and c.AccountNum = a.AccountNum
and e.Services = "newDoc"
and d.Method in ('C','D')
group by b.ClientUserID

open email_crsr
fetch email_crsr
When I try to apply the stored procedure to the database. I get the following error message
"Incorrect syntax near the keyword 'select'."

I have looked everywhere, I don't see any problem with my select statement.

Does anyone know how I can resolve this problem?


Thanks in advance for your help