In 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 using it like this:
CREATE PROCEDURE MY_PROC(@PARAM INT)
AS
SELECT * FROM MY_TABLE
Jeterson, you will be combining the worst of both worlds, a cursor and a dynamic query, but you should 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