Declaring cursor.

[Rajendra P. KASHI]

Hi,
I'm trying to define cursor but the select statement I'm preparing in a string
variable. Is their a way to create cursor for the prepared string variable.

Example:
DECLARE @Sql_String NVARCHAR(4000)
DECLARE @Incdnt_Nbr VARCHAR(50)
SELECT @Sql_String = 'SELECT Incdnt_Nbr FROM Sos_Incdnt'
DECLARE curIncdnt scroll cursor For @Sql_String

Open curIncdnt
Fetch Next From curIncdnt Into @Incdnt_Nbr
While @@Fetch_Status <> -1
Begin
Print @Incdnt_Nbr
Fetch Next From curIncdnt Into @Incdnt_Nbr
End

Close curIncdnt
Deallocate curIncdnt

If any body has gathered details on how to do this, please replay me.

Thanking You,
Regards,
Raj

[Michael Tzoanos]

Raj,

I am dying to find out the answer to that one.

Here's some code from SQL Server Books online:
(Dynamic Cursors, (Popup Selection)Declaring Cursors)

I have not been able to execute or compile the statement successfully.

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL
PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;

"Rajendra P. KASHI" <kashi_p_r@hotmail.com> wrote:
>
>Hi,
>I'm trying to define cursor but the select statement I'm preparing in a
string
>variable. Is their a way to create cursor for the prepared string variable.
>
>Example:
>DECLARE @Sql_String NVARCHAR(4000)
>DECLARE @Incdnt_Nbr VARCHAR(50)
>SELECT @Sql_String = 'SELECT Incdnt_Nbr FROM Sos_Incdnt'
>DECLARE curIncdnt scroll cursor For @Sql_String
>
> Open curIncdnt
> Fetch Next From curIncdnt Into @Incdnt_Nbr
> While @@Fetch_Status <> -1
> Begin
> Print @Incdnt_Nbr
> Fetch Next From curIncdnt Into @Incdnt_Nbr
> End
>
> Close curIncdnt
> Deallocate curIncdnt
>
>If any body has gathered details on how to do this, please replay me.
>
>Thanking You,
>Regards,
>Raj

[David Satz]

I have never tried and should read this "disclaimer" on dynamic SQL:
http://www.algonet.se/~sommar/dynamic_sql.html

However, you can create a temp table to store the results
use you dynamic SQL to insert into the temp table
and then create a regular cursor ag. the temp table

--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"Rajendra P. KASHI" <kashi_p_r@hotmail.com> wrote in message
news:3ca94d2c$1@10.1.10.29...
>
> Hi,
> I'm trying to define cursor but the select statement I'm preparing in a
string
> variable. Is their a way to create cursor for the prepared string
variable.
>
> Example:
> DECLARE @Sql_String NVARCHAR(4000)
> DECLARE @Incdnt_Nbr VARCHAR(50)
> SELECT @Sql_String = 'SELECT Incdnt_Nbr FROM Sos_Incdnt'
> DECLARE curIncdnt scroll cursor For @Sql_String
>
> Open curIncdnt
> Fetch Next From curIncdnt Into @Incdnt_Nbr
> While @@Fetch_Status <> -1
> Begin
> Print @Incdnt_Nbr
> Fetch Next From curIncdnt Into @Incdnt_Nbr
> End
>
> Close curIncdnt
> Deallocate curIncdnt
>
> If any body has gathered details on how to do this, please replay me.
>
> Thanking You,
> Regards,
> Raj