using 'IN' keyword in cursor declaration
-
hi all,
DECLARE c1 CUROSR FOR
SELECT AMOUNT, POINTS, ROUNDING, MAXPOINTS
FROM POINTS_DETAILS
WHERE PTSREFELIGID IN (@TEMP) AND PARTLOCID=@PARTLOCID
AND CAMPAIGNID=(
SELECT MAX(CAMPAIGNID) FROM POINTS_DETAILS P
WHERE P.PTSREFELIGID IN ( @TEMP ) AND P. PARTLOCID=@PARTLOCID )
AND CAMPAIGNSUBID=(
SELECT MAX(CAMPAIGNSUBID) FROM POINTS_DETAILS P1
WHERE P1.PTSREFELIGID IN ( @TEMP ) AND P1. PARTLOCID=@PARTLOCID
)
PTSREFELIGID is of type varchar
@temap is of type varchar.
if @temp variable have a single value the the cursor returns row.
if it is more than one value im getting an empty cursor
any help??
-
I guess this is expected behaviour. You can't use @TEMP to pass more than a single value. It will not be correctly interpreted as a list of values.
This might be interesting http://www.algonet.se/~sommar/arrays-in-sql.html for you.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
working fine for me... habdy functionthanks to Erland Sommarskog, and to u, for the link
I guess this is expected behaviour. You can't use @TEMP to pass more than a single value. It will not be correctly interpreted as a list of values.
This might be interesting http://www.algonet.se/~sommar/arrays-in-sql.html for you.
Frank
[/quote]
-
ksrameshbabu,
Since the variable @Temp contains multiple values you should put it in a temp TABLE variable. You can then join to it instead of using "Where in()". This works and it's fast.
Also, use a derived table to return the max ids, then join to this table on the max ids = ids (see above code).
Also, try to get rid of the cursor. If you have to loop look up "While" in BOL; it can replace most fast forward cursors.
cl
declare @Temp Table (PTSREFELIGID int)
Insert @Temp Values (NULL)--One row for each PTSREFELIGID
declare @PARTLOCID int
select pd3.AMOUNT, pd3.POINTS, pd3.ROUNDING, pd3.MAXPOINTS
From (
Select max(CAMPAIGNID) as maxCAMPAIGNID, max(CAMPAIGNSUBID) as maxCAMPAIGNSUBID
From @Temp t
JOIN POINTS_DETAILS pd1 on pd1.PTSREFELIGID = t.PTSREFELIGID
where pd0.PARTLOCID =@PARTLOCID
) pd2
JOIN POINTS_DETAILS pd3
on pd2.maxCAMPAIGNID = pd3.CAMPAIGNID
and pd2.maxCAMPAIGNSUBID = pd3.CAMPAIGNSUBID
Signature is NULL
working fine for me... habdy functionViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply