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

• Jeff Moden

  SSC Guru

  Points: 1004299

  More actions

  January 17, 2008 at 7:43 pm

  #769520

  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)
  

• David Jackson

  SSCertifiable

  Points: 6414

  More actions

  January 19, 2008 at 10:12 am

  #769952

  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

  January 19, 2008 at 10:22 am

  #769954

  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

  January 19, 2008 at 10:50 am

  #769956

  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."