Need help replacing a cursor with set-based code (if possible!)

Question

Post reply

Need help replacing a cursor with set-based code (if possible!)

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Ah... sorry... I see what you're getting at, now... yes, you are correct... only way to trully avoid the RBAR of the function is to, in fact, limit to a certain number of lines and do a bit of hardcoding as you suggested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

David Jackson SSCertifiable Points: 6414 More actions · Answer 2

PW (1/17/2008)

>>Heh... I'm thinking that's not quite true, PW... see David Jackson's post. Only limit is the 8K barrier in SS2000.

Right, it is dynamic, but it doesn't avoid a cursor or 'hidden cursor'.

....

Not saying it's wrong, it's an elegant way to solve the problem, but it has performance implications if the resultset is large.

Well the OP is happy so I am too. 🙂 But as it is a slow Saturday afternoon, I had a go at turning your code into a piece if Dynamic Code (boo, hiss) and came up with this.



Declare @sql1 varchar(4000), @sql2 varchar(4000), @sql3 varchar(4000), @sql4 varchar(4000)

Declare @maxLineNo int, @i int

select @sql1 = 'SELECT D1.SPTD_KEYNO,'

select @sql2 = ''

select @sql3 = ''

select @sql4 = ''

Select @i = 0 ,@maxLineNo = max(SPTD_LINENO) from SPTEXTDT

select @maxLineNo

While @i < @maxLineNo

Begin

	Select @i = @i + 1

	Select @sql2 = @sql2 + ' ISNULL(D' + cast(@i as varchar(5)) + '.sptd_text,'''') +'

End

Select @sql2 = Left(@sql2, Len(@sql2) - 1)

select @sql3 = ' FROM   SPTEXTDT AS D1'

Select @i = 1

While @i < @maxLineNo

BEGIN

	Select @i = @i + 1

	Select @sql4 = @sql4 + '

		LEFT JOIN SPTEXTDT AS D' +  cast(@i as varchar(5)) + '

           ON D1.SPTD_KEYNO = D' + cast(@i as varchar(5)) + '.SPTD_KEYNO

               AND D' + cast(@i as varchar(5)) + '.SPTD_LINENO = D1.SPTD_LINENO + ' + cast(@i - 1 as varchar(5))

END

--next line would be an sp_executeSQL in a SP

Select @sql1 + @sql2 + @sql3 + @sql4

(An aside. Am I the only person who has trouble copying snippets tagged like the one above with the code tag? I always lose the line breaks :angry: )

This produces



SELECT D1.SPTD_KEYNO, ISNULL(D1.sptd_text,'') + ISNULL(D2.sptd_text,'') + ISNULL(D3.sptd_text,'')  FROM   SPTEXTDT AS D1

		LEFT JOIN SPTEXTDT AS D2

           ON D1.SPTD_KEYNO = D2.SPTD_KEYNO

               AND D2.SPTD_LINENO = D1.SPTD_LINENO + 1

		LEFT JOIN SPTEXTDT AS D3

           ON D1.SPTD_KEYNO = D3.SPTD_KEYNO

               AND D3.SPTD_LINENO = D1.SPTD_LINENO + 2

which matches your query (I think). But the result set from this generated query produces all of the lines from the comments table.

[font="Courier New"]

SPTD_KEYNO

----------- ------------------------------------------------------------------------------

2661 req by Wayne @ 575-642-5555. ...

2662 req by Jose Jiminez @ 877-BASEBALL. 01/10/08: GATE WAS LOCKED ON ARRI VAL, ...

2662 VAL, RESCHEDULE. Roger Ramjet: he's our leader, fighting for our nati on. F...

2662 on. For his adventures just be sure to stay tuned to this station. ...

2663 req by S. Clause, 1-800-FTHRXMAS. ...

2659 APPROVED PLANS NOT ON SITE ...

[/font]

I've snipped the output above, but the first 2662 line is the one with all the text.

Am I missing something? (likely :P)

Did you intend to use your view in a join?

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

David Jackson SSCertifiable Points: 6414 More actions · Answer 3

David Jackson (1/19/2008)

Am I missing something? (likely :P)

Did you intend to use your view in a join?

I've answered my own question. 🙂 This does it.



Declare @sql1 varchar(4000), @sql2 varchar(4000), @sql3 varchar(4000), @sql4 varchar(4000)

Declare @maxLineNo int, @i int

select @sql1 = 'SELECT D1.SPTD_KEYNO,max('

select @sql2 = ''

select @sql3 = ''

select @sql4 = ''

Select @i = 0 ,@maxLineNo = max(SPTD_LINENO) from SPTEXTDT

select @maxLineNo

While @i < @maxLineNo

Begin

	Select @i = @i + 1

	Select @sql2 = @sql2 + ' ISNULL(D' + cast(@i as varchar(5)) + '.sptd_text,'''') +'

End

Select @sql2 = Left(@sql2, Len(@sql2) - 1)

select @sql3 = ') FROM   SPTEXTDT AS D1'

Select @i = 1

While @i < @maxLineNo

BEGIN

	Select @i = @i + 1

	Select @sql4 = @sql4 + '

		LEFT JOIN SPTEXTDT AS D' +  cast(@i as varchar(5)) + '

           ON D1.SPTD_KEYNO = D' + cast(@i as varchar(5)) + '.SPTD_KEYNO

               AND D' + cast(@i as varchar(5)) + '.SPTD_LINENO = ' + cast(@i as varchar(5))

END        

exec (@sql1 + @sql2 + @sql3 + @sql4 + ' Group by d1.SPTD_KEYNO')

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

David Jackson SSCertifiable Points: 6414 More actions · Answer 4

David Jackson (1/19/2008)

David Jackson (1/19/2008)

Am I missing something? (likely :P)

Did you intend to use your view in a join?

I've answered my own question. 🙂 This does it.

No it doesn't :angry:

Sorry, I didn't look at the results closely enough...

Well I'll let the superpeeps that lurk here fix it 😛

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."