Select qry is not working properly

23 Sep 2005 7:37 AM

K R Lal

Hello,

I am writing an sql qry for cursor function. the Select qry is not working
properly. please check this and help me

declare @m as varchar(20)
declare @mm as varchar(400)
set @m='r-01,r-002'
set @m=char(39)+replace(@m,',',char(39)+', '+char(39))+char(39)
select @m
set @mm ='select * from masteritem where refnumber in ('+ @m+ ')'
exec (@mm)
this work on normal mode but the last 2 line I want to put after declaring
the cursor. There the set command is not working.

if I do like this

declare @m as varchar(20)
declare @mm as varchar(400)
set @m='r-01,r-002'
set @m=char(39)+replace(@m,',',char(39)+', '+char(39))+char(39)
select @m
select * from masteritem where refnumber in ( @m)

no result

Please help me.

Thank and regards

Lal

23 Sep 2005 10:03 AM

Nik Marshall-Blank

I can't see any CURSOR declarations here,please post complete code.

--
Nik Marshall-Blank MCSD/MCDBA

Show quote

"K R Lal" <l***@yahoo.com> wrote in message
news:u2UNFGBwFHA.3300@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I am writing an sql qry for cursor function. the Select qry is not working
> properly. please check this and help me
>
> declare @m as varchar(20)
> declare @mm as varchar(400)
> set @m='r-01,r-002'
> set @m=char(39)+replace(@m,',',char(39)+', '+char(39))+char(39)
> select @m
> set @mm ='select * from masteritem where refnumber in ('+ @m+ ')'
> exec (@mm)
> this work on normal mode but the last 2 line I want to put after declaring
> the cursor. There the set command is not working.
>
> if I do like this
>
> declare @m as varchar(20)
> declare @mm as varchar(400)
> set @m='r-01,r-002'
> set @m=char(39)+replace(@m,',',char(39)+', '+char(39))+char(39)
> select @m
> select * from masteritem where refnumber in ( @m)
>
> no result
>
> Please help me.
>
> Thank and regards
>
> Lal
>
>

23 Sep 2005 10:09 AM

David Portas

To implement a dynamic cursor declaration requires either that you
create a global cursor or that you use sp_executesql to return a cursor
variable. Of course the smart solution is usually to avoid using
cursors at all...

--
David Portas
SQL Server MVP
--