Dynamic SQL into a cursor

basically im creating a user defined function that i want to take the parameters that are passed to it to create the sql for a cursor to loop through. As far as i can tell a cursor cannot take dynamic sql, anyone got any bright ideas or an easy way round this?

My code so far is as follows (the variable @.sql is the sql statement i want passed in to the cursor):

--##########TESTING VALUES#########--
declare @.inpfieldname as varchar(20)
declare @.inptable as varchar(50)
declare @.inprefno as int
set @.inpfieldname = 'disch_dttm'
set @.inptable = 'provider_spells'
set @.inprefno = 100604947
--#################################--

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

set @.sql = 'select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

declare archivecur cursor
for
@.sql

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
right i thought id found the answer to append the rows from the dynamic sql into a table variable and loop through that but unfortunately im now getting must declare the variable @.curtable

--##########TESTING VALUES#########--
declare @.inpfieldname as varchar(20)
declare @.inptable as varchar(50)
declare @.inprefno as int
set @.inpfieldname = 'disch_dttm'
set @.inptable = 'provider_spells'
set @.inprefno = 100604947
--#################################--

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

declare @.curtable table (Field1 varchar(50), modifdate varchar(8))

set @.sql = 'insert into @.curtable (field1, modifdate) convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + convert(varchar(20),@.inprefno) + ' order by modif_dttm'

execute(@.sql)

declare archivecur cursor
for
select field1, modifdate
from @.curtable
order by modifdate

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
|||

Lamffy,

In order to execute dynamic sql, you need EXEC(...) or sp_executesql and no one of them could be used inside a user defined function.

To play with a cursor and dynamic sql , you can define and open the cursor inside the dynamic sql and return it in an output variable.

Example:

Code Snippet

use northwind

go

declare @.sql nvarchar(4000)

declare @.customerid nchar(5)

declare @.c cursor

declare @.orderid int

set @.sql = N'

set @.c = cursor local fast_forward

for

select orderid

from dbo.orders

where customerid = @.customerid;

open @.c'

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

if cursor_status('variable', '@.c') = 1

begin

while 1 = 1

begin

fetch next from @.c into @.orderid

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

print @.orderid

end

close @.c

deallocate @.c

end

what are you trying to accomplish, creating a user defined function that involves dynamic sql and cursors?

AMB

|||

Try

set @.sql = 'declare archivecur cursor for
select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

exec (@.sql)

open archivecur

|||

Maybe it's me, but this seems like extreme overkill.

What function or looping is required to carry out this task?

I'm thinking a SELECT CASE would accomplish what you are looking for.

Adamus

|||Cheers Mark that worked. You're a lifesaver!! :-)
|||I agree Adamus it does look a bit like overkill but when you see the state of NHS data and the data warehouses it's all stored in you'd understand how much of a nightmare doing anything is!