|
|
Posted by aCe on 09/24/07 04:16
On Sep 22, 9:00 pm, "Dan Guzman" <guzma...@nospam-
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
>
> <?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" <acerah...@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
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
Navigation:
[Reply to this message]
|