Hi

I have a cursor that I need to pass a dynamic sql statement to that is in a
variable, is this possible?

e.g. DECLARE curOneRecord CURSOR FOR @SQL

Thanks
B

Both cursors and dynamic SQL are considered bad practices. Anyhow, if you still want to do this:

EXEC('DECLARE curOneRecord CURSOR FOR ' + SQL)


Show quoteHide quote

Ben,

Do the contrary, open the cursor inside the dynamic sql statement.

Example:

declare @c cursor
declare @sql nvarchar(4000)

set @sql = N'
set @c = cursor for
select
    orderid, customerid, orderdate
from
    dbo.orders
where
    customerid = @customerid; open @c'

exec sp_executesql @sql, N'@c cursor output, @customerid nchar(5)', @c
output, 'alfki'

if cursor_status('variable', '@c') = 1
    begin
    while 1 = 1
        begin
        fetch next from @c

        if @@error != 0 or @@fetch_status != 0 break
        end
    end

if cursor_status('variable', '@c') >= 0
    close @c

deallocate @c
go

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Did you try to find a set-based solution before using cursors?


AMB

Show quoteHide quote

an example ofr dynamic
sql statement. It might help


use northwind
declare @test nvarchar(4000)
declare @values nvarchar (200)
set @values='1,2,3,4'
set @test='select * from employees where employeeid in ('+ (@values)+')'
EXEC(@test)



Show quoteHide quote

Time Difference Calculations
Customers & Phones
Return only the Third Row
Choosing one record over another
can't return zero when no rows exist using aggregate function sum