creating a stored procedure-- help

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
SteveIf 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
"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_connectio
n);
> $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
>|||
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|||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:

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

Create View in a Select statement ?

Greetings all. I'm a complete SQL novice, and well, I'm not even using
SQL, but I have a dBase control which utilizes SQL SELECT statements to
pull a rowset from a .DBF table.
I have a small table with a few fields, but for this query I only need
to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
the SELECT statement to return a rowset with the fields NAME and EMAIL,
where each entry for EMAIL1 or EMAIL2 will be output to it's own row.
example--
Orignal Table:
NAME EMAIL1 EMAIL2
ted ted@.x.com
chris chris@.a.com chris@.b.com
Query Result:
NAME EMAIL
ted ted@.x.com
chris chris@.a.com
chris chris@.b.com
Any ideas?
Much Thanks,
Christian K.Christian K wrote:

> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
Select Name, Email1 as Email from Table
Union
Select Name, Email2 as Email from Table where Email2 IS NOT NULL
HTH,
Stijn Verrept.|||select name, email1 as email from table
union
select name, email2 as email from table
order by name
This will eliminate duplicates as for example when, let's say Ted, has the
same email in email1 and email2.
Use Union All is you don't want to eliminate duplicates.
"Christian K" <ChristianK@.HeroData.com> wrote in message
news:ua%23YskD$FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements to
> pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and EMAIL,
> where each entry for EMAIL1 or EMAIL2 will be output to it's own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.
>|||If your SQL qry component will enable you to use the keyword union, here is
a
way...
create table email (name varchar(20) ,email1 varchar(50) ,email2 varchar(50)
)
insert email
select name = 'Jeff' ,email1 = 'me@.com.com' ,email2 = null
insert email
select name = 'Dave' ,email1 = 'dave@.com.com' ,email2 = 'david@.me.com'
select name ,email = email1
from email
where email1 is not null
union
select name ,email = email2
from email
where email2 is not null
order by name
drop table email
HTH
JeffP....
"Christian K" <ChristianK@.HeroData.com> wrote in message
news:ua%23YskD$FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements to
> pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and EMAIL,
> where each entry for EMAIL1 or EMAIL2 will be output to it's own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.
>|||Thank you both, it worked.
Christian K wrote:

> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements
> to pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and
> EMAIL, where each entry for EMAIL1 or EMAIL2 will be output to it's
> own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.|||Thank you both, it worked.
Christian K wrote:

> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements
> to pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and
> EMAIL, where each entry for EMAIL1 or EMAIL2 will be output to it's
> own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.