PHP code to Stored Procedure

hi all,
i need to convert these simple PHP code into stored procedure :
$result = mssql_query( "SELECT whid, whcode FROM warehouse" );
while( $wh = mssql_fetch_object( $result ) )
{
$result = mssql_query( "SELECT plid, nopl FROM packlist WHERE whid =
'" . $wh->whid . "'";
while( $pl = mssql_fetch_object( $result ) )
{
$result = mssql_query( "SELECT qty FROM packlistnmat WHERE plid =
'" . $pl->plid . "'";
while( $pln = mssql_fetch_object( $result ) )
{
echo "Stock from " . $wh->whcode . " AND Packing List number " .
$pl->plid . " = " . $pln->qty;
}
}
}
?>
my focus is in nested query, then i can call each field from the query
(SELECT whid, whcode...) in sub query.
thanks,
aCe

Re: PHP code to Stored Procedure

> i need to convert these simple PHP code into stored procedure :

I don't know PHP but you can JOIN the related tables and encapsulate the
query in a stored procedure like the untested example below. You'll often
get best performance by joining related tables on the back-end rather than
performing for-each processing in application code.

CREATE PROCECURE dbo.usp_GetPackingLists
AS
SELECT
w.whcode,
pl.plid,
pln.qty
FROM dbo.warehouse AS w
JOIN packlist AS pl ON w.whid = pl.whid
JOIN packlistnmat AS pln ON pln.plid = pl.plid
GO

$result = mssql_query( "EXEC dbo.usp_GetPackingLists" );
while( $wh = mssql_fetch_object( $result ) )
{
echo "Stock from " . $wh->whcode . " AND Packing List number " .
$pl->plid . " = " . $pln->qty;
}
?>

--
Hope this helps.

Dan Guzman
SQL Server MVP



"aCe" wrote in message
news:1190444269.094724.14270@57g2000hsv.googlegroups.com...
> hi all,
> i need to convert these simple PHP code into stored procedure :
> > $result = mssql_query( "SELECT whid, whcode FROM warehouse" );
> while( $wh = mssql_fetch_object( $result ) )
> {
> $result = mssql_query( "SELECT plid, nopl FROM packlist WHERE whid =
> '" . $wh->whid . "'";
> while( $pl = mssql_fetch_object( $result ) )
> {
> $result = mssql_query( "SELECT qty FROM packlistnmat WHERE plid =
> '" . $pl->plid . "'";
> while( $pln = mssql_fetch_object( $result ) )
> {
> echo "Stock from " . $wh->whcode . " AND Packing List number " .
> $pl->plid . " = " . $pln->qty;
> }
> }
> }
> ?>
> my focus is in nested query, then i can call each field from the query
> (SELECT whid, whcode...) in sub query.
> thanks,
> aCe
>

Re: PHP code to Stored Procedure

On Sep 22, 9:00 pm, "Dan Guzman" online.sbcglobal.net> wrote:
> > i need to convert these simple PHP code into stored procedure :
>
> I don't know PHP but you can JOIN the related tables and encapsulate the
> query in a stored procedure like the untested example below. You'll often
> get best performance by joining related tables on the back-end rather than
> performing for-each processing in application code.
>
> CREATE PROCECURE dbo.usp_GetPackingLists
> AS
> SELECT
> w.whcode,
> pl.plid,
> pln.qty
> FROM dbo.warehouse AS w
> JOIN packlist AS pl ON w.whid = pl.whid
> JOIN packlistnmat AS pln ON pln.plid = pl.plid
> GO
>
> > $result = mssql_query( "EXEC dbo.usp_GetPackingLists" );
> while( $wh = mssql_fetch_object( $result ) )
> {
> echo "Stock from " . $wh->whcode . " AND Packing List number " .
> $pl->plid . " = " . $pln->qty;}
>
> ?>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "aCe" wrote in message
>
> news:1190444269.094724.14270@57g2000hsv.googlegroups.com...
>
> > hi all,
> > i need to convert these simple PHP code into stored procedure :
> > > > $result = mssql_query( "SELECT whid, whcode FROM warehouse" );
> > while( $wh = mssql_fetch_object( $result ) )
> > {
> > $result = mssql_query( "SELECT plid, nopl FROM packlist WHERE whid =
> > '" . $wh->whid . "'";
> > while( $pl = mssql_fetch_object( $result ) )
> > {
> > $result = mssql_query( "SELECT qty FROM packlistnmat WHERE plid =
> > '" . $pl->plid . "'";
> > while( $pln = mssql_fetch_object( $result ) )
> > {
> > echo "Stock from " . $wh->whcode . " AND Packing List number " .
> > $pl->plid . " = " . $pln->qty;
> > }
> > }
> > }
> > ?>
> > my focus is in nested query, then i can call each field from the query
> > (SELECT whid, whcode...) in sub query.
> > thanks,
> > aCe

thanks for your reply Dan Guzman.
but my query more complex than above. :D
coz i'm a newby in MSSQL, i need to optimize my query using stored
procedure.

can help me further more, thx before... :D

Re: PHP code to Stored Procedure

aCe wrote:

> thanks for your reply Dan Guzman.
> but my query more complex than above. :D
> coz i'm a newby in MSSQL, i need to optimize my query using stored
> procedure.
>
> can help me further more, thx before... :D

Unless you plan on posting the more complex query, I doubt he
can. The telepathic version of MSSQL is still about five years
away from RTM.

(joke stolen from the MAS 90 support forums, obviously can be
applied to any software you like)

Re: PHP code to Stored Procedure

> thanks for your reply Dan Guzman.
> but my query more complex than above. :D
> coz i'm a newby in MSSQL, i need to optimize my query using stored
> procedure.

Do you have another query? I included the stored procedure code in my
response based on the queries and code you provided. If you are having
trouble extending the solution, we'll need more information to help.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"aCe" wrote in message
news:1190607404.896418.244680@19g2000hsx.googlegroups.com...
> On Sep 22, 9:00 pm, "Dan Guzman" > online.sbcglobal.net> wrote:
>> > i need to convert these simple PHP code into stored procedure :
>>
>> I don't know PHP but you can JOIN the related tables and encapsulate the
>> query in a stored procedure like the untested example below. You'll
>> often
>> get best performance by joining related tables on the back-end rather
>> than
>> performing for-each processing in application code.
>>
>> CREATE PROCECURE dbo.usp_GetPackingLists
>> AS
>> SELECT
>> w.whcode,
>> pl.plid,
>> pln.qty
>> FROM dbo.warehouse AS w
>> JOIN packlist AS pl ON w.whid = pl.whid
>> JOIN packlistnmat AS pln ON pln.plid = pl.plid
>> GO
>>
>> >> $result = mssql_query( "EXEC dbo.usp_GetPackingLists" );
>> while( $wh = mssql_fetch_object( $result ) )
>> {
>> echo "Stock from " . $wh->whcode . " AND Packing List number " .
>> $pl->plid . " = " . $pln->qty;}
>>
>> ?>
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "aCe" wrote in message
>>
>> news:1190444269.094724.14270@57g2000hsv.googlegroups.com...
>>
>> > hi all,
>> > i need to convert these simple PHP code into stored procedure :
>> > >> > $result = mssql_query( "SELECT whid, whcode FROM warehouse" );
>> > while( $wh = mssql_fetch_object( $result ) )
>> > {
>> > $result = mssql_query( "SELECT plid, nopl FROM packlist WHERE whid =
>> > '" . $wh->whid . "'";
>> > while( $pl = mssql_fetch_object( $result ) )
>> > {
>> > $result = mssql_query( "SELECT qty FROM packlistnmat WHERE plid =
>> > '" . $pl->plid . "'";
>> > while( $pln = mssql_fetch_object( $result ) )
>> > {
>> > echo "Stock from " . $wh->whcode . " AND Packing List number " .
>> > $pl->plid . " = " . $pln->qty;
>> > }
>> > }
>> > }
>> > ?>
>> > my focus is in nested query, then i can call each field from the query
>> > (SELECT whid, whcode...) in sub query.
>> > thanks,
>> > aCe
>
> thanks for your reply Dan Guzman.
> but my query more complex than above. :D
> coz i'm a newby in MSSQL, i need to optimize my query using stored
> procedure.
>
> can help me further more, thx before... :D
>