June 15, 2010 at 9:40 am
Hello Forum,
I have what I think is a very basic Stored Proc question, i think. I am trying to do a join on the results from a stored proc.
Here is a little more specific information.
I have this Stored procedure in SQL 2005. - and I need to rewrite it to be compatible with SQL 2000 which does not support the With statement. any Ideas how to approach this? I was thinking of using a view, and doing a select and a join on the results, but I could really use useful suggestions from this forum. Thanks in advance,
Greg
WITH tAdjustmentEx
(Id, AdjustmentHeaderId, AdjustmentDetailId,
TypeCode, MeasurementId, Value, Row)
AS
(
SELECT
A.Id, D.AdjustmentHeaderId, A.AdjustmentDetailId,
D.TypeCode, A.MeasurementId, A.Value, A.Row
FROM tAdjustment AS A
INNER JOIN tAdjustmentDetail AS D ON
A.AdjustmentDetailId=D.Id AND D.AdjustmentHeaderId=@AdjustmentId
)
SELECT
I.Id AS InitId, I.Id AS DispId, I.AdjustmentHeaderId, I.AdjustmentDetailId, I.MeasurementId,
I.Value AS InitAmount, D.Value AS DispAmount
FROM tAdjustmentEx AS I
LEFT OUTER JOIN tAdjustmentEx AS D ON
I.AdjustmentHeaderId=D.AdjustmentHeaderId AND
I.MeasurementId=D.MeasurementId AND
D.TypeCode='D'
WHERE I.TypeCode IN ('I', 'S')
ORDER BY I.Row
June 15, 2010 at 9:47 am
A view would work. The WITH indicates a CTE, which is essentially a materialized view for that query.
You could use a derived table as well, however I think it's less readable:
SELECT
I.Id AS InitId, I.Id AS DispId, I.AdjustmentHeaderId, I.AdjustmentDetailId, I.MeasurementId,
I.Value AS InitAmount, D.Value AS DispAmount
FROM ((
SELECT
A.Id, D.AdjustmentHeaderId, A.AdjustmentDetailId,
D.TypeCode, A.MeasurementId, A.Value, A.Row
FROM tAdjustment AS A
INNER JOIN tAdjustmentDetail AS D ON
A.AdjustmentDetailId=D.Id AND D.AdjustmentHeaderId=@AdjustmentId
)
) tAdjustmentEx AS I
LEFT OUTER JOIN
((
SELECT
A.Id, D.AdjustmentHeaderId, A.AdjustmentDetailId,
D.TypeCode, A.MeasurementId, A.Value, A.Row
FROM tAdjustment AS A
INNER JOIN tAdjustmentDetail AS D ON
A.AdjustmentDetailId=D.Id AND D.AdjustmentHeaderId=@AdjustmentId
)
) tAdjustmentEx AS D ON
I.AdjustmentHeaderId=D.AdjustmentHeaderId AND
I.MeasurementId=D.MeasurementId AND
D.TypeCode='D'
WHERE I.TypeCode IN ('I', 'S')
ORDER BY I.Row
June 15, 2010 at 2:23 pm
How would I accomplish this with a variable in my criteria such as @AdjustmentId? I could see the view working without this, but can't figure out how to make a view that uses the variable in it's creation of final output criteria?
June 15, 2010 at 2:37 pm
You don't put the variable in the view. You'd have:
CREATE VIEW tAdjustmentEx
as
SELECT
A.Id, D.AdjustmentHeaderId, A.AdjustmentDetailId,
D.TypeCode, A.MeasurementId, A.Value, A.Row
FROM tAdjustment AS A
INNER JOIN tAdjustmentDetail AS D
ON A.AdjustmentDetailId=D.Id
And then
SELECT
I.Id AS InitId, I.Id AS DispId, I.AdjustmentHeaderId, I.AdjustmentDetailId, I.MeasurementId,
I.Value AS InitAmount, D.Value AS DispAmount
FROM tAdjustmentEx AS I
LEFT OUTER JOIN tAdjustmentEx AS D ON
I.AdjustmentHeaderId=D.AdjustmentHeaderId AND
I.MeasurementId=D.MeasurementId AND
D.TypeCode='D'
WHERE I.TypeCode IN ('I', 'S')
I.AdjustmentHeaderId=@AdjustmentId
ORDER BY I.Row
June 15, 2010 at 2:46 pm
Thanks for that logic!:-D
I was thinking the exact same thing, but I guess I was making it more complicated than it had to be because of the way the With Clause is coded to filter the header ID using a variable. I can do that filter outside the view and get the same dataset result.
I really appreciate your help in solving this!
June 15, 2010 at 2:51 pm
My pleasure, and good luck here. Not sure if I'd use the CTE for future versions here. If you are using it a lot, then it might be nice as a view in SQL 2K5 as well.
June 15, 2010 at 2:57 pm
Just an observation: since your stored procedure doesn't take any parameters, it doesn't really need to be a stored procedure. The SP could itself be a view.
--J
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply