creating a stored procedure-- help

8 Sep 2005 5:21 PM

ahoy hoy

im novice to sqlserver and stored procedures.
Can the php code below be converted to a stored procedure

$y=1;

$query = "Select * From tblNews Order By aOrder";
$result = mysql_query($query,$db_connection);
$NoRows = mysql_num_rows($result);
if ($NoRows != 0 )
{
    while ($row = mysql_fetch_array($result))
    {
        $UpdateQuery = "Update tblNews
        Set aOrder= $y
        Where ID=".$row["ID"];
        mysql_query($UpdateQuery,$db_connection);
        $y++;
    }
}

basically, i want select all from tblNews, order by aOrder
then update aOrder in each record starting at 1 and incrementing by 1
untill all the records have been processed

Can anyone help me please, is it possible

thanks in advance
Steve

8 Sep 2005 5:53 PM

Alejandro Mesa

If all values in column [aOrder] are diff, then you can try,

update tblNews
set aOrder = (select count(*) from tblNews as a where a.aOrder <=
tblNews.aOrder)

AMB

Show quote

"ahoy hoy" wrote:

> im novice to sqlserver and stored procedures.
> Can the php code below be converted to a stored procedure
>
> $y=1;
>
> $query = "Select * From tblNews Order By aOrder";
> $result = mysql_query($query,$db_connection);
> $NoRows = mysql_num_rows($result);
> if ($NoRows != 0 )
> {
>     while ($row = mysql_fetch_array($result))
>     {
>         $UpdateQuery = "Update tblNews
>         Set aOrder= $y
>         Where ID=".$row["ID"];
>         mysql_query($UpdateQuery,$db_connection);
>         $y++;
>     }
> }
>
>
> basically, i want select all from tblNews, order by aOrder
> then update aOrder in each record starting at 1 and incrementing by 1
> untill all the records have been processed
>
> Can anyone help me please, is it possible
>
> thanks in advance
> Steve
>

8 Sep 2005 6:02 PM

Barry

Steve,

You could use something along the lines of this.... (Un-Tested)

Create Proc TestProcedure

As Begin

    Declare @ID Integer
    Declare @NewOrder Integer

    Set @NewOrder = 1

    Declare OrderCursor Cursor For

        Select ID From tblNews
        Order By aOrder

    Open OrderCursor

    Fetch Next From OrderCursor Into @ID

    While @@Fetch_Status = 0

        Begin

            Update tblNews
            Set aOrder = @NewOrder
            Where ID = @ID

            Set @NewOrder = @NewOrder + 1

            Fetch Next From OrderCursor Into @ID

        End

Close OrderCursor
Deallocate OrderCursor

End
Go

Although if it is a huge amount of Data and performance is an issue
then I would probably not use a Cursor.

Hope this helps

Barry

9 Sep 2005 12:23 AM

ahoy hoy

Barry
thank you so much!
i wouldve been trying to figure that out for days, it is exactly what i
needed.

Just needed to use the correct field names and rename Interger to Int,
proc to procedure!

Now i can finish my job

Its only for a small amount of data, 10-20 records

Awesome

Steve :)

Barry wrote:

Show quote

>
> Steve,
>
>
> You could use something along the lines of this.... (Un-Tested)
>
>
>
> Create Proc TestProcedure
>
> As Begin
>
>
>     Declare @ID Integer
>     Declare @NewOrder Integer
>
>     Set @NewOrder = 1
>
>
>     Declare OrderCursor Cursor For
>
>         Select ID From tblNews
>         Order By aOrder
>
>
>     Open OrderCursor
>
>     Fetch Next From OrderCursor Into @ID
>
>
>     While @@Fetch_Status = 0
>
>         Begin
>
>
>             Update tblNews
>             Set aOrder = @NewOrder
>             Where ID = @ID
>
>             Set @NewOrder = @NewOrder + 1
>
>             Fetch Next From OrderCursor Into @ID
>
>         End
>
> Close OrderCursor
> Deallocate OrderCursor
>
>
> End
> Go
>
>
>
> Although if it is a huge amount of Data and performance is an issue
> then I would probably not use a Cursor.
>
> Hope this helps
>
> Barry
>