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 4 3366
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" <ac*******@gmail.comwrote in message
news:11*********************@57g2000hsv.googlegrou ps.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
On Sep 22, 9:00 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.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_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.comwrote in message
news:11*********************@57g2000hsv.googlegrou ps.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
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)
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" <ac*******@gmail.comwrote in message
news:11**********************@19g2000hsx.googlegro ups.com...
On Sep 22, 9:00 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.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_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.comwrote in message
news:11*********************@57g2000hsv.googlegro ups.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rhino |
last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a
Throwable back to the calling program as an OUT parameter?
If yes, what datatype should I use when registering the...
|
by: Thomasb |
last post by:
With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.
I have...
|
by: Stephen Noronha |
last post by:
Hi,
I get an error "System.Data.SqlClient.SqlException", after my Stored
Procedure runs and i saw an article for debugging through the Stored
procedure using VS .Net....
|
by: Woody Splawn |
last post by:
I am using SQL Server 2000 as the back-end. I have created a stored
procedure in SQL server called usp_AddContract. This Stored procedure
inserts a new contract into a contracts table. I have...
|
by: Antonio |
last post by:
hello, I have a data grid being populated from any number of stored
procedures. I need to have the totals in the footer, so I use the
ItemDataBound event to sum up and display the results.
this...
|
by: Crash |
last post by:
C#
VS 2003
..Net Framework V1.1 SP1
SQL Server 2000 SP3
Enterprise Library June 2005
I'm working with some code {not of my creation} that performs the
following sequence of actions:
- Open...
|
by: jed |
last post by:
I have created this example in sqlexpress
ALTER PROCEDURE .
@annualtax FLOAT
AS
BEGIN
SELECT begin1,end1,deductedamount,pecentageextra
FROM tax
|
by: preejith |
last post by:
Error Code : 1329, No data - zero rows fetched, selected, or processed. MYSQL
I am getting the following error while running a stored procedure in mysql5.0
Error Code : 1329
No data - zero rows...
|
by: Joey Martin |
last post by:
One of my servers got hacked with the SQL injection due to poor coding.
So, I had someone write a stored procedure and new code.
But, to me, it looks just as flawed, even using the stored...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |