PHP code to Stored Procedure

[aCe]

hi all,
i need to convert these simple PHP code into stored procedure :
<?php
$result = mssql_query( "SELECT whid, whcode FROM warehouse" );
while( $wh = mssql_fetch_obj ect( $result ) )
{
$result = mssql_query( "SELECT plid, nopl FROM packlist WHERE whid =
'" . $wh->whid . "'";
while( $pl = mssql_fetch_obj ect( $result ) )
{
$result = mssql_query( "SELECT qty FROM packlistnmat WHERE plid =
'" . $pl->plid . "'";
while( $pln = mssql_fetch_obj ect( $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

[Dan Guzman]

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

<?php
$result = mssql_query( "EXEC dbo.usp_GetPack ingLists" );
while( $wh = mssql_fetch_obj ect( $result ) )
{
echo "Stock from " . $wh->whcode . " AND Packing List number " .
$pl->plid . " = " . $pln->qty;
}
?>

--
Hope this helps.

Dan Guzman
SQL Server MVP



"aCe" <acerahmat@gmai l.comwrote in message
news:1190444269 .094724.14270@5 7g2000hsv.googl egroups.com...

hi all,
i need to convert these simple PHP code into stored procedure :
<?php
$result = mssql_query( "SELECT whid, whcode FROM warehouse" );
while( $wh = mssql_fetch_obj ect( $result ) )
{
$result = mssql_query( "SELECT plid, nopl FROM packlist WHERE whid =
'" . $wh->whid . "'";
while( $pl = mssql_fetch_obj ect( $result ) )
{
$result = mssql_query( "SELECT qty FROM packlistnmat WHERE plid =
'" . $pl->plid . "'";
while( $pln = mssql_fetch_obj ect( $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
>

[aCe]

Re: PHP code to Stored Procedure

On Sep 22, 9:00 pm, "Dan Guzman" <guzma...@nospa m-
online.sbcgloba l.netwrote:

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_GetPack ingLists
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
>
<?php
$result = mssql_query( "EXEC dbo.usp_GetPack ingLists" );
while( $wh = mssql_fetch_obj ect( $result ) )
{
echo "Stock from " . $wh->whcode . " AND Packing List number " .
$pl->plid . " = " . $pln->qty;}
>
?>
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"aCe" <acerah...@gmai l.comwrote in message
>
news:1190444269 .094724.14270@5 7g2000hsv.googl egroups.com...
>

hi all,
i need to convert these simple PHP code into stored procedure :
<?php
$result = mssql_query( "SELECT whid, whcode FROM warehouse" );
while( $wh = mssql_fetch_obj ect( $result ) )
{
$result = mssql_query( "SELECT plid, nopl FROM packlist WHERE whid =
'" . $wh->whid . "'";
while( $pl = mssql_fetch_obj ect( $result ) )
{
$result = mssql_query( "SELECT qty FROM packlistnmat WHERE plid =
'" . $pl->plid . "'";
while( $pln = mssql_fetch_obj ect( $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

[Ed Murphy]

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)

[Dan Guzman]

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" <acerahmat@gmai l.comwrote in message
news:1190607404 .896418.244680@ 19g2000hsx.goog legroups.com...

On Sep 22, 9:00 pm, "Dan Guzman" <guzma...@nospa m-
online.sbcgloba l.netwrote:

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_GetPack ingLists
>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
>>
><?php
>$result = mssql_query( "EXEC dbo.usp_GetPack ingLists" );
>while( $wh = mssql_fetch_obj ect( $result ) )
>{
> echo "Stock from " . $wh->whcode . " AND Packing List number " .
> $pl->plid . " = " . $pln->qty;}
>>
>?>
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"aCe" <acerah...@gmai l.comwrote in message
>>
>news:119044426 9.094724.14270@ 57g2000hsv.goog legroups.com...
>>

hi all,
i need to convert these simple PHP code into stored procedure :
<?php
$result = mssql_query( "SELECT whid, whcode FROM warehouse" );
while( $wh = mssql_fetch_obj ect( $result ) )
{
$result = mssql_query( "SELECT plid, nopl FROM packlist WHERE whid =
'" . $wh->whid . "'";
while( $pl = mssql_fetch_obj ect( $result ) )
{
$result = mssql_query( "SELECT qty FROM packlistnmat WHERE plid =
'" . $pl->plid . "'";
while( $pln = mssql_fetch_obj ect( $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
>