Sequence numbers without a cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sequence numbers without a cursor?

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-08-15 : 11:39:12

Hi gang! Haven't been here in a while..
I'm getting through my second SQL training school, doing all kinds of stored procedure work. I'm just finishing up a rather lengthy procedure that inserts rows into a table which requires a sequence number as part of the clustered key. I've set this up to be done with a cursor, and it works, but is there a way to do this without a cursor? How would you advance the sequence number for each row? Or is this a stupid question

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 11:59:59

U may do it with identity column

or do u want something like :

USE Pubs
SELECT COUNT(e1.emp_id) as [record #], e1.emp_id , e1.fname, e1.lname
FROM employee AS e1
INNER JOIN employee AS e2 ON e1.emp_id >= e2.emp_id
group by e1.emp_id , e1.fname , e1.lname
order by e1.emp_id

Srinika

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-08-15 : 17:22:33

Hey Srinika,
Actually what I have is a select query that groups item numbers together by vendor number. I then create a row in PO_HDR for the vendor info and then insert each item to be ordered into PO_ORD_LIN.
SO:(air code)

INSERT INTO PO_ORD_LIN
(VEND_NO,ITEM_NO,QTY_ORD,TKT_NO,ORD_SEQ_NO)
VALUES('FOO','BLAH','1','1234',HERE IS THE NUMBER TO INCREMENT)

The seq_no needs to start at '1' for each PO created for each vendor, so it must be reset each time we reach a new PO.
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 08:15:12

Try :


Declare @i int
Set @i = Select count(*) + 1 from PO_ORD_LIN

INSERT INTO PO_ORD_LIN
(VEND_NO,ITEM_NO,QTY_ORD,TKT_NO,ORD_SEQ_NO)
VALUES('FOO','BLAH','1','1234',@i)

Srinika

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-08-16 : 08:24:51

Ok!
There's a way to look at it, but how do I advance the seq_no for each line to be inserted for each po line? Won't this just count the po lines, add 1, and then insert that value for all of the rowsets?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-16 : 12:32:32

Where do you want to show data?
If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-16 : 13:05:48

INSERT INTO PO_ORD_LIN
(VEND_NO,ITEM_NO,QTY_ORD,TKT_NO,ORD_SEQ_NO)
select 'FOO','BLAH','1','1234',(Select count(*) + 1 from PO_ORD_LIN)

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-08-16 : 13:59:11

Hey Madhivanan & Spirit!
Ok, let me be more precise to clear up the confusion:
the task here is to make a procedure that selects all lines from a ticket line table (PS_TKT_LIN) and insert them into a purchase request line table (PO_PREQ_LIN). each item in PS_TKT_LIN has a vendor id number, and we need to add the items to the PO_PREQ_LIN table using this vendor ID, the item number, and a sequence number (along with other columns, but these are the important ones). We need to

select max(seq_no) 
from po_preq_lin
where vend_no=ps_tkt_lin.vend_no

and then
insert into po_preq_lin all items for that vendor id while incrementing each sequence number.
I wrote this procedure here at work, and took it to
my instructor to show him; this started the whole
"cursors have a use sometimes" crowd (me) vs. "cursors cause global warming and should be banned" crowd (my instructor). Basically the attitude is no cursors, no way; But I can't seem to see a way to do this otherwise. How would we loop through records? Keep in mind that during the select of ticket lines, there will be more than one vendor, so we need to reset the sequence number each time we change vendors.
thoughts?

There's never enough time to type code right,
but always enough time for a hotfix...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-16 : 14:25:38

well one way is to update the column after the insert.
look here:
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 1

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 14:56:18

My First post in this thread can be modified to cater this:

Use Pubs

Create table #t (a int, b varchar(20), c varchar(100), d varchar(100))
Insert into #t
SELECT COUNT(e1.emp_id) as [record #], e1.emp_id , e1.fname, e1.lname
FROM employee AS e1
INNER JOIN employee AS e2 ON e1.emp_id >= e2.emp_id
group by e1.emp_id , e1.fname , e1.lname
order by e1.emp_id

Select * from #t

Drop table #t

Srinika

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-16 : 21:07:35

Sounds like a bit of a non-requirement here, or rather one with irrelevancies. Why does the sequence number need to be per-vendor? Isn't your goal to distinguish (or assign a PK) to each row in po_preq_lin? In which case a single identity will certainly do the job. You won't see what you expect, but as madhivanan said, it's just a presentation issue after that. If you're on SQL Server 2005 then you can query it out using row_number or rank to make it look purdy.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-18 : 16:08:32

Lozt,

I think the OP is looking for line numbers for the lines on the invoices. I do that in an application that processes medical claims, where a claim form can have 1 or more lines. So, each line is attached to the same claim number, but incremented to indicate the additional line:


CLAIM  LINE
 0001   01
 0001   02
 0001   03
 0002   01
 0003   01
 0003   02

This allows us to logically keep the individual lines attached to the claim form it came in on, and also gives us a key value (claim + line) to uniquely identify each claim/line combination.

Ken

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-21 : 19:41:26

I understand what he's after but think he's over specified it. A globally unique line# - i.e. an identity- will achieve all he needs it to except for the aethetics of having it sequential starting from & incrementing by 1. This can all be done either on the front end or when it is selected. It is not a requirement that is part of the data model.

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-08-21 : 21:30:25

You can use a WHILE loop instead of a cursor. You might need to use nested WHILE loops.

This is something I might do... which might prove to be over-engineered or inefficient.


DECLARE @ClaimNum int, @Seq int
DECLARE @TableWithDistinctRecords table
(
  ID int identity primary key
  ,Claim varchar(10)
  ,NumOccurrences int
)

INSERT INTO @TableWithDistinctRecords (Claim, NumOccurrences)
SELECT
  Claim
  ,COUNT(Claim)
FROM ClaimTable
GROUP BY 
  Claim

SET @ClaimNum = 1

WHILE @ClaimNum <= (SELECT MAX(ID) FROM @TableWithDistinctRecords)
BEGIN

  SET @Seq = 1
  WHILE @Seq <= (SELECT NumOccurrences FROM @TableWithDistinctRecords WHERE ID = @ClaimNum)
  BEGIN
    INSERT INTO Whatever (blah, Seq)
    SELECT
      blahValue
      ,@Seq

    SET @Seq = @Seq + 1
  END
  SET @ClaimNum = @ClaimNum + 1
END

I hope that makes some sense...

::Edit::
I forgot to mention a reason for using that table variable (which could just as easily be a temporary table)

Especially when working with a large dataset, it is pretty quick to enumerate the number of Claims, and then assign an identity value per claim. Using this table in the loop allows the loop to be very well defined. Otherwise, you might do something like SET @ClaimNum = (SELECT MIN(Claim) FROM ClaimTable WHERE Claim > @ClaimNum). This requires a lookup every time the loop ends. Using a static table as a reference-point speeds things up dramatically.

Sometimes using While loops can actually be slower than using cursors, although they are generally faster.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-08-24 : 20:42:05

Hey Gang,
Had to do some travel, but I'm back. I agree that this is a bit of overkill, but it is something that has been written into the app and I need to work with it. The sequencial order of the lines per PO is the way the app requires the data. Spirit, I can't update the column after the insert, as there are constraints through the app that force a sequential insert. i know this is a pain in the rear, but must be done none the less. rav3n0u5's idea seems pretty good, so now the age old question comes up:
Should I go this way, or leave the cursor in the proc as it stands? Which will produce the most efficient operations?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-08-25 : 02:15:23

Can you provide some sample data for the PS_TKT_LIN table.
And also state the rules on which the seq_no is calculated, or maybe that is just the order they come in the PS_TKT_LIN table?

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-25 : 02:21:26

quote:

Originally posted by Srinika

Declare @i int
Set @i = Select count(*) + 1 from PO_ORD_LIN

INSERT INTO PO_ORD_LIN
(VEND_NO,ITEM_NO,QTY_ORD,TKT_NO,ORD_SEQ_NO)
VALUES('FOO','BLAH','1','1234',@i)

Declare @i int
Set @i = Select count(*) + 1 from PO_ORD_LIN WHERE VEND_NO =  'Foo'

INSERT INTO PO_ORD_LIN
(VEND_NO,ITEM_NO,QTY_ORD,TKT_NO,ORD_SEQ_NO)
VALUES('FOO','BLAH','1','1234',@i)

quote:
Originally posted by spirit1

INSERT INTO PO_ORD_LIN
(VEND_NO,ITEM_NO,QTY_ORD,TKT_NO,ORD_SEQ_NO)
select 'FOO','BLAH','1','1234',(Select count(*) + 1 from PO_ORD_LIN)

INSERT INTO PO_ORD_LIN
(VEND_NO,ITEM_NO,QTY_ORD,TKT_NO,ORD_SEQ_NO)
select 'FOO','BLAH','1','1234',(Select count(*) + 1 from PO_ORD_LIN WHERE VEND_NO = 'foo')

Peter Larsson
Helsingborg, Sweden

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-08-25 : 16:46:55

Just run a performance test. That should give you a solid answer.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-25 : 22:55:57

I believe you want the dense_Rank function. Here is a example of how it works

SELECT DENSERANK() OVER
(ORDER BY LOCK_WAIT_TIME DESC) AS RANK,
AUTHID, LOCK_WAIT_TIME AS AVG_WAIT
FROM LOCK_SNAP
ORDER BY RANK

RANK AUTHID AVG_WAIT
------ --------- -----------
1 OMERZA 10
1 SMITH 10
2 BEULKE 2
3 FELSER 1
3 KOMER 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-26 : 00:16:41

Questioner didnt specify he/she is using SQL Server 2005

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-08-26 : 03:08:19

There are two opinions in the thread I support:

quote:
Originally posted by madhivanan
If you use front end application, do numbering there

When it is not presentation issue then I support:

quote:
cursors have a use sometimes

Finally I wrote the article about that, it may be useful here: http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources