|
Home > Archive > MS SQL Server > April 2006 > Stored procedure question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stored procedure question
|
|
| Ron Hinds 2006-04-03, 8:23 pm |
| Here is my stored procedure:
CREATE PROCEDURE updateSpecialServer
@post_date varchar(20)
AS
-- SET NOCOUNT ON
-- Create a local table
DECLARE @UpdateSpecial TABLE (
[TargetAudience] varchar(50),
[PartNumber] varchar(50),
[QtySold] int,
[DollarsSold] money
);
-- Populate the table with the ALL first
INSERT INTO @UpdateSpecial (TargetAudience, PartNumber, QtySold,
DollarsSold)
SELECT SpecialServer.PartNumber, SpecialServer.TargetAudience,
Sum([OrderDetailHST].[QtyOrd]) AS QtySold,
Sum([OrderDetailHST].[QtyOrd]*[OrderDetailHST].[Price]) AS DollarsSold
FROM (SpecialServer INNER JOIN OrderDetailHST ON SpecialServer.PartNumber =
OrderDetailHST.ItemID) INNER JOIN OrderHdHST ON OrderDetailHST.InvoiceNumber
= OrderHdHST.InvoiceNumber
WHERE OrderHdHST.& #91;Date]=@post_date
AND SpecialServer.TargetAudience LIKE
'ALL%'
GROUP BY SpecialServer.PartNumber, SpecialServer.TargetAudience;
INSERT INTO @UpdateSpecial (TargetAudience, PartNumber, QtySold,
DollarsSold)
SELECT SpecialServer.PartNumber, SpecialServer.TargetAudience,
Sum([OrderDetailHST].[QtyOrd]) AS QtySold,
Sum([OrderDetailHST].[QtyOrd]*[OrderDetailHST].[Price]) AS DollarsSold
FROM (SpecialServer INNER JOIN OrderDetailHST ON SpecialServer.PartNumber =
OrderDetailHST.ItemID) INNER JOIN OrderHdHST ON OrderDetailHST.InvoiceNumber
= OrderHdHST.InvoiceNumber AND SpecialServer.TargetAudience =
OrderHdHST.CustID
WHERE OrderHdHST.& #91;Date]=@post_date
GROUP BY SpecialServer.PartNumber, SpecialServer.TargetAudience;
UPDATE S SET QtySold = U.QtySold, DollarsSold = U.DollarsSold FROM
SpecialServer S INNER JOIN @UpdateSpecial U ON S.TargetAudience =
U.TargetAudience AND S.PartNumber = U.PartNumber;
GO
It isn't updating the SpecialServer table. But if I manually run the SELECT
from the first INSERT statement using the same parameter, it returns 15
rows. I've run it in the QA debugger and don't get any errors. How can I
view the values in the in-memory table when in the debugger? Should I change
it to a regular temp table? If so, how would I view the values in that
table?
| |
| Edgardo Valdez, MCSD, MCDBA 2006-04-03, 8:23 pm |
| I would execute the procedure as a script, as follows: changing
CREATE PROCEDURE updateSpecialServer
@post_date varchar(20)
AS
to
declare @post_date varchar(20)
set @post_date varchar(20) = [the test value]
then replace the last update to a join:
UPDATE S SET QtySold = U.QtySold, DollarsSold = U.DollarsSold FROM
SpecialServer S INNER JOIN @UpdateSpecial U ON S.TargetAudience =
U.TargetAudience AND S.PartNumber = U.PartNumber;
GO
to
Select * from
SpecialServer S
INNER JOIN @UpdateSpecial U ON S.TargetAudience =
U.TargetAudience AND S.PartNumber = U.PartNumber;
GO
and
before the last update, I will display the values of the @UpdateSpecial:
select * from @UpdateSpecial
Then execute the code as a script, which it will show the values of the
@UpdateSpecial so you can see why it does not match with the SpecialServer
table based on the join parameters.
You can use temporary tables, if you want to keep using the result to
continue testing against the last join. I would recommend to use local
temporary tables (#) which will be dropped when your session ends.
Let me know if it helps...
"Ron Hinds" wrote:
> Here is my stored procedure:
>
> CREATE PROCEDURE updateSpecialServer
> @post_date varchar(20)
> AS
> -- SET NOCOUNT ON
>
> -- Create a local table
> DECLARE @UpdateSpecial TABLE (
> [TargetAudience] varchar(50),
> [PartNumber] varchar(50),
> [QtySold] int,
> [DollarsSold] money
> );
>
> -- Populate the table with the ALL first
> INSERT INTO @UpdateSpecial (TargetAudience, PartNumber, QtySold,
> DollarsSold)
> SELECT SpecialServer.PartNumber, SpecialServer.TargetAudience,
> Sum([OrderDetailHST].[QtyOrd]) AS QtySold,
> Sum([OrderDetailHST].[QtyOrd]*[OrderDetailHST].[Price]) AS DollarsSold
> FROM (SpecialServer INNER JOIN OrderDetailHST ON SpecialServer.PartNumber =
> OrderDetailHST.ItemID) INNER JOIN OrderHdHST ON OrderDetailHST.InvoiceNumber
> = OrderHdHST.InvoiceNumber
> WHERE OrderHdHST.& #91;Date]=@post_date
AND SpecialServer.TargetAudience LIKE
> 'ALL%'
> GROUP BY SpecialServer.PartNumber, SpecialServer.TargetAudience;
>
> INSERT INTO @UpdateSpecial (TargetAudience, PartNumber, QtySold,
> DollarsSold)
> SELECT SpecialServer.PartNumber, SpecialServer.TargetAudience,
> Sum([OrderDetailHST].[QtyOrd]) AS QtySold,
> Sum([OrderDetailHST].[QtyOrd]*[OrderDetailHST].[Price]) AS DollarsSold
> FROM (SpecialServer INNER JOIN OrderDetailHST ON SpecialServer.PartNumber =
> OrderDetailHST.ItemID) INNER JOIN OrderHdHST ON OrderDetailHST.InvoiceNumber
> = OrderHdHST.InvoiceNumber AND SpecialServer.TargetAudience =
> OrderHdHST.CustID
> WHERE OrderHdHST.& #91;Date]=@post_date
> GROUP BY SpecialServer.PartNumber, SpecialServer.TargetAudience;
>
> UPDATE S SET QtySold = U.QtySold, DollarsSold = U.DollarsSold FROM
> SpecialServer S INNER JOIN @UpdateSpecial U ON S.TargetAudience =
> U.TargetAudience AND S.PartNumber = U.PartNumber;
> GO
>
> It isn't updating the SpecialServer table. But if I manually run the SELECT
> from the first INSERT statement using the same parameter, it returns 15
> rows. I've run it in the QA debugger and don't get any errors. How can I
> view the values in the in-memory table when in the debugger? Should I change
> it to a regular temp table? If so, how would I view the values in that
> table?
>
>
>
>
| |
| Ron Hinds 2006-04-03, 8:23 pm |
| Thanks, that was a tremendous help! I transposed two of the column names
hence the final join was never working (doh!).
<Edgardo Valdez>; <MCSD>; "MCDBA"
< EdgardoValdezMCSDMCD
BA@discussions.microsoft.com> wrote in message
news:B14192CF-7892-425A-A915- 6D31EAE21072@microso
ft.com...[color=darkred]
> I would execute the procedure as a script, as follows: changing
>
> CREATE PROCEDURE updateSpecialServer
> @post_date varchar(20)
> AS
>
> to
>
> declare @post_date varchar(20)
> set @post_date varchar(20) = [the test value]
>
> then replace the last update to a join:
>
> UPDATE S SET QtySold = U.QtySold, DollarsSold = U.DollarsSold FROM
> SpecialServer S INNER JOIN @UpdateSpecial U ON S.TargetAudience =
> U.TargetAudience AND S.PartNumber = U.PartNumber;
> GO
>
> to
>
> Select * from
> SpecialServer S
> INNER JOIN @UpdateSpecial U ON S.TargetAudience =
> U.TargetAudience AND S.PartNumber = U.PartNumber;
> GO
>
> and
>
> before the last update, I will display the values of the @UpdateSpecial:
>
> select * from @UpdateSpecial
>
> Then execute the code as a script, which it will show the values of the
> @UpdateSpecial so you can see why it does not match with the SpecialServer
> table based on the join parameters.
>
> You can use temporary tables, if you want to keep using the result to
> continue testing against the last join. I would recommend to use local
> temporary tables (#) which will be dropped when your session ends.
>
> Let me know if it helps...
>
> "Ron Hinds" wrote:
>
SpecialServer.PartNumber =[color=darkred]
OrderDetailHST. InvoiceNumber[color=
darkred]
LIKE[color=darkred]
SpecialServer.PartNumber =[color=darkred]
OrderDetailHST. InvoiceNumber[color=
darkred]
SELECT[color=darkred
]
change[color=darkred
]
|
|
|
|
|