Enhance Stored Procedure

Question

Post reply

Enhance Stored Procedure

kytan

SSC Rookie

Points: 30
More actions
October 29, 2004 at 1:35 am

#63552

Hi All, I have the table which contain 7 Fields (CustID, D01, D02, D03, D04, D05, StartAt)

Example
                CUSTID   D01 D02 D03 D04 D05
                A               0       0       1      1        0
                B               1       1        0     1        0
                C               0       0        0     0        1
                D               1       1        1     1        1

I need to write a Stored procedure to detect the first field contain data greater then 0, from D01 to D05
Example
                CUSTID   StartAt
                A                 3           --(detect D3 is the first column > 0)
                B                  1          --(detect D1 is the first column > 0)
                C                  5          --(D5)
                D                  1          --(D1)

Can anyone suggest a better way to update data? Below is my testing stored procedure

Declare @cus as nvarchar(100)
Declare @StrNo int

DECLARE @rs CURSOR
SET @rs = CURSOR LOCAL FAST_FORWARD FOR
           SELECT CustID From Table1
OPEN @rs

FETCH NEXT FROM @rs INTO @cus

WHILE @@FETCH_STATUS = 0
BEGIN
                                Set @StrNo = 0
                                Select
                                                @StrNo = Case
                                                when Sum(D01) > 0 Then 1
                                                when Sum(D02) > 0 Then 2
                                                when Sum(D03) > 0 Then 3
                                                when Sum(D04) > 0 Then 4
                                                when Sum(D05) > 0 Then 5
                                                End
                                From
                                                Table1
                                WHERE
                                                Table1.Custid = @cus
                                GROUP BY
                                                Table1.Custid,

                                Update Table1Set StartAs = @StrNo
                                WHERE Table1.Custid = @cus

                                FETCH NEXT FROM @rs INTO @cus
END
CLOSE @rs
DEALLOCATE @rs

--Sorry for my poor english

Viewing 7 posts - 1 through 6 (of 6 total)

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

Why use a CURSOR at all?

Why not just:

UPDATE Tbl

SET StartAs =

CASE

WHEN D01 > 0 THEN 1

WHEN D02 > 0 THEN 2

WHEN D03 > 0 THEN 3

WHEN D04 > 0 THEN 4

WHEN D05 > 0 THEN 5

ELSE 0

END

--
Adam Machanic
whoisactive

kytan SSC Rookie Points: 30 More actions · Answer 2

It’s because some of the reasons i need to use CURSOR.

Anyway thank for your replied

Adam Machanic SSCoach Points: 15370 More actions · Answer 3

What reasons?

There is almost never a good reason to use a cursor, especially in SQL Server 2000, with UDFs, etc.

If you want to get rid of it, please post more detail.

--
Adam Machanic
whoisactive

kytan SSC Rookie Points: 30 More actions · Answer 4

Actually I have 2 primary key which i didn't mention in the earlier post. looking at the table, i only want to group the 1st primary key only so that i get the result i want.

Table Structure

CustID (Primary Key)

DataType (Primary Key)

D01

D02

D03

D04

D05

StartAt

Data Contain

CustID DataType D01 D02 D03 D04 D05

A 1 0 0 1 1 1

A 2 0 0 0 1 1

A 3 0 0 0 0 1

B 1 1 0 0 0 0

B 2 0 1 0 1 0

C 1 0 0 0 0 1

D 1 1 1 1 1 1

I want to get the result like

CustID StartAt

A 3 (Detect D3 is the first column > 0 when group by CustID)

B 1

C 5

D 1

I already re-design my stored procedure, can u please help me to find out is this is a better way

Select Custid,

StartAt = Case

when Sum(D01) > 0 Then 1

when Sum(D02) > 0 Then 2

when Sum(D03) > 0 Then 3

when Sum(D04) > 0 Then 4

when Sum(D05) > 0 Then 5

Else 0

End

Into #Temp1

From Table1

Group by Custid

Update Table1 Set StartAt =

(

Select StartAt From #Temp1

Where #Temp1.CustID = Table1.CustID

)

Adam Machanic SSCoach Points: 15370 More actions · Answer 5

Personally, I would get rid of the temp table and just do:

Update Table1

Set StartAt =

(

Select case when Sum(D01) > 0 Then 1

when Sum(D02) > 0 Then 2

when Sum(D03) > 0 Then 3

when Sum(D04) > 0 Then 4

when Sum(D05) > 0 Then 5

Else 0

End

From Table1 T1

Where T1.CustID = Table1.CustID

)

... but, whatever works for you

--
Adam Machanic
whoisactive

kytan SSC Rookie Points: 30 More actions · Answer 6

Thanks for the solution. It helps a lot.

Thanks.