CURSOR Question 1

[webuser]

This is the usual way a cursor is declared:
DECLARE cursor_name
FOR select_statement ...

Is there a way to use a string variable in place of the select_statement?

In other words, my SQL statement depends upon parameters that are entered when my Stored Procedure is called. I want to be able to run the Cursor with the SQL string that is created using those parameters. Thanks in advance.

 

[tlbroadbent]

Unlike temporary tables and variables, cursors are declared for the session. They can be declared in dynamic SQL and referenced outside the scope of the EXEC() statement.

Example:

DECLARE
@cols varchar(100),
@tbl varchar(20),
@crit varchar(12),
@v1 varchar(40),
@v2 varchar(40),
@v3 varchar(6)

SET @tbl='employees'
SET @cols = 'EmpName, EmpEmail, EmpID'
SET @crit = 2134

EXEC
('DECLARE test CURSOR FOR' +
' SELECT ' + @cols +
' FROM ' + @tbl +
' WHERE EmpID=' + @crit)

OPEN test
FETCH NEXT FROM test INTO @v1, @v2, @v3

SELECT @v1,@v2,@v3

CLOSE test
DEALLOCATE test Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[webuser]

Thank you very much. This is exactly what I needed.