June 19, 2010 at 5:29 am
I have a stored procedure with a sub-query which uses Case..When..Else as follows:
CREATE PROCEDURE StoredProcedure1
AS
SELECT Id, EntryDate, Product, ProdPrice, Qty, Rtn,
(SELECT CASE [X].Rtn
WHEN 0 THEN
SUM([X].[Qty] * [X].[ProdPrice])
ELSE
SUM([X].[Qty] * [X].[ProdPrice] * -1)
FROM dbo.Table1 AS X WHERE X.Product = dbo.Table1.Product AND X.EntryDate <= dbo.Table1.EntryDate) AS ProductRunningSum
FROM dbo.Table1
RETURN
When I try to save this stored procedure, I get:
ADO error: Incorrect syntax near the keyword 'FROM'
As I've been staring at this for a while now can someone please put me out of my misery ! :crying: (I expect it's really simple to correct).
June 19, 2010 at 6:52 am
You're missing the END from the CASE
CASE
WHEN ...
ELSE ...
END
CREATE PROCEDURE StoredProcedure1
AS
SELECT
Id, EntryDate, Product, ProdPrice, Qty, Rtn,
(SELECT
CASE [X].Rtn
WHEN 0 THEN SUM([X].[Qty] * [X].[ProdPrice])
ELSE SUM([X].[Qty] * [X].[ProdPrice] * -1)
END
FROM dbo.Table1 AS X WHERE X.Product = dbo.Table1.Product AND X.EntryDate <= dbo.Table1.EntryDate)
AS ProductRunningSum
FROM dbo.Table1
GO
Just as an aside, this is not going to perform well on larger row counts (and by 'large' I mean a few thousand rows). That triangular join kills performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2010 at 7:12 am
Thank you Gail,
I'm trying to create a running sum in the stored procedure, which I've managed to do in an Access .mdb as follows:
SELECT Table1.Id, Table1.EntryDate, Table1.Product, Table1.ProdPrice, Table1.Qty, Table1.Rtn, (SELECT Sum(IIf(X.Rtn=False,[X].[Qty]*[X].[ProdPrice],[X].[Qty]*[X].[ProdPrice]*-1)) FROM Table1 AS X WHERE X.Product = Table1.Product AND X.EntryDate <= Table1.EntryDate) AS ProductRunningSum
FROM Table1
ORDER BY Table1.EntryDate;
Which works fine.
Can you offer any advice on how to do this in a Stored Procedure and maintain performance when there is a large row count ?
Even with the End added, I've found that I have to group by [X].Rtn to be able to save the SP and even then, it fails to run due to there being more than one row returned by the subquery for some reason (the data is exactly the same as it is in the .mdb file).
TIA.
EDIT:
I've now managed to get the SP working as it should:
ALTER PROCEDURE StoredProcedure1
AS
SELECT Id, EntryDate, Product, ProdPrice, Qty, Rtn,
(SELECT Sum(CASE [X].Rtn
WHEN 0 THEN
[X].[Qty] * [X].[ProdPrice]
ELSE
[X].[Qty] * [X].[ProdPrice] * -1
END)
FROM dbo.Table1 AS X WHERE X.Product = dbo.Table1.Product AND X.EntryDate <= Table1.EntryDate) AS ProductRunningSum
FROM dbo.Table1
RETURN
All I need to know now is how it should be done !
TIA for any advice
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy