|
|
Posted by Dan Guzman on 09/25/07 01:43
> 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@gmail.com> wrote in message
news:1190607404.896418.244680@19g2000hsx.googlegroups.com...
> 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]
|