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

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 (AT) gmail (DOT) com> wrote

On Sep 22, 9:00 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
thanks for your reply Dan Guzman.
but my query more complex than above.
coz i'm a newby in MSSQL, i need to optimize my query using stored
procedure.

can help me further more, thx before...

aCe wrote:

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)

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 (AT) gmail (DOT) com> wrote