|
|
Posted by Dan Guzman on 09/22/07 14:00
> 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
<?php
$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" <acerahmat@gmail.com> wrote in message
news:1190444269.094724.14270@57g2000hsv.googlegroups.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_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
>
Navigation:
[Reply to this message]
|