Help in stored procedures - SQL Server Forums

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)

Help in stored procedures

Author

Topic

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-20 : 06:26:14

Category ClassID SeqNbr

SQUARE TUBING 000001 1
RECTANGULAR TUBING 000542 2
TUBING SPECIALS 000543 3
TUBING SPECIALS RUSTY 001042 5
PLASTIC PLUGS 000544 7

If u see the seqnbr falls to 5 for category Tubing specials rusty and 7 for Plastic Plugs.
Actually 4 have to be present in place of 5 and 5 in place of 7 in the seqnbr.

For that have to write stored procedures

We have to result the seqnbr in asc order not random if we use the select stmt.
Please help the logic or in code that how to write procedures to get result for this.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 06:32:47

Here's one method...

--data
declare @t table (Category varchar(30), ClassID varchar(10), SeqNbr int)
insert @t
          select 'SQUARE TUBING', '000001', 1 
union all select 'RECTANGULAR TUBING', '000542', 2 
union all select 'TUBING SPECIALS', '000543', 3
union all select 'TUBING SPECIALS RUSTY', '001042', 5
union all select 'PLASTIC PLUGS', '000544', 7

--calculation
update a set SeqNbr = (select count(*) from @t where SeqNbr <= a.SeqNbr) from @t a

select * from @t

/*
Category                       ClassID    SeqNbr      
------------------------------ ---------- ----------- 
SQUARE TUBING                  000001     1
RECTANGULAR TUBING             000542     2
TUBING SPECIALS                000543     3
TUBING SPECIALS RUSTY          001042     4
PLASTIC PLUGS                  000544     5
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 06:36:18

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

Madhivanan

Failing to plan is Planning to fail

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-20 : 07:58:02

Table is available already with 500 rows. Rows consists of unordered seqnbr. We could touch or update category only thing is we have to update the seqnbr starting from 1 till end in asc order.

Maddy this is an already entered through front end application.

But now the client want the seqnbr to be in order without entering data once again. How to proceed

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 08:07:29

gurusamy.senthil - did you see my post? The code there updates the seqnbr starting from 1 according to the order of the existing seqnbr data. Is that not what you wanted?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 09:15:43

>>But now the client want the seqnbr to be in order

How does the client view data, thru application or from file where you export data?
If you dont use any front end application, try the method suggested by Ryan. But this is not advisable if the table has thousands of rows

Madhivanan

Failing to plan is Planning to fail

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-20 : 09:27:17

Ok maddy.

I will try and come back.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-20 : 11:50:13

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Homework

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-21 : 01:03:30

Hi ryan,
You are joining all the values by using Union. But there are large amount of data, So we cannot use Union all operator for everyone.

The below code is what I tried.

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE id = object_id('dbo.xCattmp') and sysstat & 0xf = 4)
DROP PROCEDURE xCattmp
GO
CREATE PROCEDURE xCattmp
AS
--DECLARE @cat varchar(10)
DECLARE @Var1 smallint
SET @Var1 = '1'
DECLARE currxCat CURSOR FOR
SELECT * from xcategorytmp order by seqnbr
OPEN currxCat
FETCH NEXT FROM currxCat --into @cat,@var1
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATExcategorytmp
SET seqnbr = @var1
FROM xcategorytmp
SET @Var1 = @Var1 + '1'
FETCH NEXT FROM currxCat
END
CLOSE currxCat
DEALLOCATE currxCat
GO

The Update stmt I used inside the While loop will be updating all the record. Actually I want to update the seqnbr line by line.
I used Cursors to fetch record line by line. Will anyone seniors can help me to come out this problem.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-21 : 03:07:46

Why cant you simulate the logic that Ryan suggested?

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-21 : 05:03:27

gurusamy.senthil - I've only used union to prepare the data. You don't need to worry about that because you already have the data in your table. You just need to use the calculation bit (the 1-line update statement)...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-22 : 03:18:42

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE id = object_id('dbo.xCat_Order_SeqNbr') and sysstat & 0xf = 4)
DROP PROCEDURE xCat_Order_SeqNbr
GO
CREATE PROCEDURE xCat_Order_SeqNbr
AS
DECLARE @SeqNbr int
DECLARE @ClassID varchar(6)
SET @SeqNbr = 1
DECLARE xCategory CURSOR FOR
SELECT ClassID from xcategory order by Seqnbr
OPEN xCategory
FETCH NEXT FROM xCategory INTO @ClassID
WHILE @@fetch_status = 0
BEGIN
UPDATE xcat
SET xcat.SeqNbr = @SeqNbr
FROM xcategory xcat
WHERE xcat.ClassID = @ClassID
SET @SeqNbr = @SeqNbr + 1
FETCH NEXT FROM xCategory INTO @ClassID
END
CLOSE xCategory
DEALLOCATE xCategory
GO

Thanks ryan and maddy. The above code gives me the result, but the thing is I have used Cursors. Any other better solution instead of using the cursors to get the result.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-22 : 03:21:24

"The above code gives me the result, but the thing is I have used Cursors. Any other better solution instead of using the cursors to get the result."

Did't Ryan has given you the solution ?

quote:
update a set SeqNbr = (select count(*) from @t where SeqNbr <= a.SeqNbr) from @t a

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-22 : 03:26:41

It will give the result but the scenerio is suppose if there is repetated seqnbr in an table Will that code works?.

I tried with and examples given by ryan by giving repeated seqnbr, it is not working

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-22 : 03:32:42

Can you post the sample data of the scenario that you describe ?

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-22 : 03:36:34

khtan I tried with the sample data given my ryan and changed the some number for repetation. And I didn't understand how the incrementing seqnbr processing. Will u please help me?

for eg:-
declare @t table (Category varchar(30), ClassID varchar(10), SeqNbr int)
insert @t
select 'SQUARE TUBING', '000001', 1
union all select 'RECTANGULAR TUBING', '000542', 2
union all select 'TUBING SPECIALS', '000543', 3
union all select 'TUBING SPECIALS RUSTY', '001042', 3
union all select 'PLASTIC PLUGS', '000544', 7

--calculation
update a set SeqNbr = (select count(*) from @t where SeqNbr <= a.SeqNbr) from @t a

select * from @t

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-22 : 06:11:14

gurusamy.senthil - A small modification will deal with duplicate SeqNbrs. Do you have any other issues with this approach?

--data
declare @t table (Category varchar(30), ClassID varchar(10), SeqNbr int)
insert @t
          select 'SQUARE TUBING', '000001', 1
union all select 'RECTANGULAR TUBING', '000542', 3
union all select 'TUBING SPECIALS', '000543', 3
union all select 'TUBING SPECIALS RUSTY', '001042', 5
union all select 'PLASTIC PLUGS', '000544', 7

--calculation
update a set SeqNbr = 
  (select count(*) from @t where SeqNbr < a.SeqNbr or (SeqNbr = a.SeqNbr and ClassID <= a.ClassID))
from @t a

select * from @t

/*
Category                       ClassID    SeqNbr      
------------------------------ ---------- ----------- 
SQUARE TUBING                  000001     1
RECTANGULAR TUBING             000542     2
TUBING SPECIALS                000543     3
TUBING SPECIALS RUSTY          001042     4
PLASTIC PLUGS                  000544     5
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-22 : 06:34:52

Thanks ryan it is working, but tell me What is the logic behind this code.

How the seqnbr is incrementing automatically without setting any prior declaration. What is happening inside the code if u don't mine please explain it. I will be greatfull to you all my friends.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-22 : 06:45:42

Great!

Okay - I'll try to explain....

For each row in the table, we're counting how many rows have a SeqNbr less then that row (or have the same SeqNbr and a ClassId less than or equal to the other rows). So take, for example, our '3rd' row (with ClassID 000543 and SeqNbr 3):

* # rows that have a SeqNbr less then that row = 1 (our '1st' row)
* # rows that have the same SeqNbr and a ClassId less than or equal to the other rows = 2 (our '2nd' and our '3rd' row)

So, the total for our '3rd' row is 3 (1 + 2). Try this for any row and you'll see that it always gives you what you need.

I hope that helps

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-22 : 06:48:31

Sorry it will not work if the seqnbr are repeated in random orders.

For eg:
--data
declare @t table (Category varchar(30), ClassID varchar(10), SeqNbr int)
insert @t
select 'SQUARE TUBING', '000001', 2
union all select 'RECTANGULAR TUBING', '000542', 2
union all select 'TUBING SPECIALS', '000543', 3
union all select 'TUBING SPECIALS RUSTY', '001042', 3
union all select 'PLASTIC PLUGS', '000544', 2

--calculation
update a set SeqNbr =
(select count(*) from @t where SeqNbr < a.SeqNbr or (SeqNbr = a.SeqNbr and ClassID <= a.ClassID))
from @t a

select * from @t.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-22 : 07:01:16

Eh? That ranks the rows 1-5 with no duplicates - just ask you asked and as I'd expect. Why do you think this does not work?

You must have some criteria to determine the ranking based on the data alone. Remember that sql server does not 'store' the data in any particular order.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources