Return a CURSOR from a Sqlserver using dynamic sql (sp_executesql)

Question

In the oracle I can do as follows :

OPEN MY_CURSOR FOR 'SELECT * FROM MY_TABLE'

How to get this result in Sqlsever? I want to return a cursor by mounting a dynamic sql in the Procedure. I’m currently wearing it like this:

CREATE PROCEDURE MY_PROC(@PARAM INT)
AS
SELECT * FROM MY_TABLE

Answer 1

Jeterson, you will be combining the worst of both worlds, a cursor and a dynamic query, but you must know the need..

Look at this example:

declare @sSQL nvarchar(4000)
Declare @des_Name VarChar(200)

set @sSQL = 'Declare Teste_cursor CURSOR FOR Select name from sys.objects'

exec sp_executesql @sSQL

OPEN Teste_cursor
FETCH NEXT FROM Teste_cursor
INTO @des_Name

WHILE @@FETCH_STATUS = 0
BEGIN
Print @des_Name


FETCH NEXT FROM Teste_cursor
INTO @des_Name

END
CLOSE Teste_cursor
DEALLOCATE Teste_cursor