Make a Dynamic Cursor in a Stored Procedure

I need im my aplication to meke a "Cursor" in a execution of a stored procedure.

For sample:

In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.

How can I do ?

My code:

Declare @.idRelat int, @.cmd_FROM nvarchar(1024), @.cmd_Det nvarchar(50)
SELECT @.idRelat = idRelat, @.cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @.p_Relat

Declare @.Tot_Col smallint, @.Tot_Lin smallint, @.Campos smallint,
@.Aux_Select nvarchar(1024), @.Aux_Group nvarchar(1024), @.Aux_Order nvarchar(1024)

Select @.Tot_Col = 0
Select @.Tot_Lin = 0
Select @.Campos = 0
Select @.Aux_Select = "SELECT " + @.cmd_DET + "AS Soma"
Select @.Aux_Group = "GROUP BY "
Select @.Aux_Order = "ORDER BY "
Declare @.a_Local char(1), @.a_Linha smallint, @.a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @.idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @.a_Local, @.a_Linha, @.a_Campo
While @.@.FETCH_status = 0 begin
If @.a_Local = "C"
Select @.Tot_Col = @.Tot_Col + 1
Else
Select @.Tot_Lin = @.Tot_Lin + 1
Select @.Campos = @.Campos + 1
If @.Aux_Group <> "GROUP BY " begin
Select @.Aux_Group = @.Aux_Group + ", "
If @.Aux_Order <> "ORDER BY " begin
Select @.Aux_Order = @.Aux_Order + ", "
Select @.Aux_Select = sSelect + ", " + @.a_Campo + " AS Campo" + @.Campos
Select @.Aux_Group = @.Aux_Group + @.a_Campo
Select @.Aux_Order = @.Aux_Order + @.a_Campo
Fetch cur_Aux into @.a_Local, @.a_Linha, @.a_Campo
End
Select @.Aux_Select = @.Aux_Select
-- <<<< MONTA COMANDO SQL
Select @.Aux_Select = @.Aux_Select + " " + @.cmd_FROM + " " + @.p_Filtro + " " + @.Aux_Group + " " + @.Aux_Order
Declare @.Cursor_Aux cursor
Set @.Cursor_Aux = cursor for @.Aux_Select
Open @.Cursor_Aux

Not working !!!!

Luiz Amrico wrote:

I need im my aplication to meke a "Cursor" in a execution of a stored procedure.

For sample:

In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.

How can I do ?

My code:

Declare @.idRelat int, @.cmd_FROM nvarchar(1024), @.cmd_Det nvarchar(50)
SELECT @.idRelat = idRelat, @.cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @.p_Relat

Declare @.Tot_Col smallint, @.Tot_Lin smallint, @.Campos smallint,
@.Aux_Select nvarchar(1024), @.Aux_Group nvarchar(1024), @.Aux_Order nvarchar(1024)

Select @.Tot_Col = 0
Select @.Tot_Lin = 0
Select @.Campos = 0
Select @.Aux_Select = "SELECT " + @.cmd_DET + "AS Soma"
Select @.Aux_Group = "GROUP BY "
Select @.Aux_Order = "ORDER BY "
Declare @.a_Local char(1), @.a_Linha smallint, @.a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @.idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @.a_Local, @.a_Linha, @.a_Campo
While @.@.FETCH_status = 0 begin
If @.a_Local = "C"
Select @.Tot_Col = @.Tot_Col + 1
Else
Select @.Tot_Lin = @.Tot_Lin + 1
Select @.Campos = @.Campos + 1
If @.Aux_Group <> "GROUP BY " begin
Select @.Aux_Group = @.Aux_Group + ", "
If @.Aux_Order <> "ORDER BY " begin
Select @.Aux_Order = @.Aux_Order + ", "
Select @.Aux_Select = sSelect + ", " + @.a_Campo + " AS Campo" + @.Campos
Select @.Aux_Group = @.Aux_Group + @.a_Campo
Select @.Aux_Order = @.Aux_Order + @.a_Campo
Fetch cur_Aux into @.a_Local, @.a_Linha, @.a_Campo
End
Select @.Aux_Select = @.Aux_Select
-- <<<< MONTA COMANDO SQL
Select @.Aux_Select = @.Aux_Select + " " + @.cmd_FROM + " " + @.p_Filtro + " " + @.Aux_Group + " " + @.Aux_Order
Declare @.Cursor_Aux cursor
Set @.Cursor_Aux = cursor for @.Aux_Select
Open @.Cursor_Aux

Not working !!!!

Your missing some keywords in your cursor sytax:

Fetch Next From cur_Aux into @.a_Local, @.a_Linha, @.a_Campo

And you're not closing and deallocating your cursor:

CLOSE cur_Aux

DEALLOCATE cur_Aux

Use this syntax:

DECLARE @.AuthorID char(11)

DECLARE c1 CURSOR FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @.AuthorID

WHILE @.@.FETCH_STATUS = 0
BEGIN

PRINT @.AuthorID

FETCH NEXT FROM c1
INTO @.AuthorID

END

CLOSE c1
DEALLOCATE c1

Adamus