تفاوت دو sp

[fan2005]

با سلام ،
تفاوت این دو stored procedure چیست که اولی نتیجه مورد نظر را بر میگرداد ولی دومی خیر؟


نمونه اطلاعات اطلاعات attach شده است.





ALTER PROCEDURE dbo.StoredProcedure1

AS

declare @temp table
(
ProductID1 int ,
ProductName1 nvarchar(50),
Description1 nvarchar(100),
Price1 decimal(18,0)
)
INSERT @temp (ProductID1 ,ProductName1 ,Description1 ,Price1 )
SELECT
Products.ProductID,
Products.ProductName,
SUBSTRING(Products.Description, 1, 150) + '...' AS Description,
Price.Price
FROM Price INNER JOIN Products ON Price.ProductID =Products.ProductID and (Price.EndDate IS NULL)
order by Products.ProductID

SELECT
ProductID1,
ProductName1,
SUBSTRING(Description1, 1, 150) + '...' AS Description1,
Price1,
ProductImage.ProductImage,
ProductImage.ProductImageID
FROM @temp INNER JOIN ProductImage ON ProductImage.ProductID=ProductID1
ORDER BY ProductID

RETURN

Output
ProductID ProductName Description Price ProductImage ProductImageID
1 Product1 Desc1...... 1000 <BINARY> 1
2 Product2 Desc2...... 6000 <BINARY> 2
2 Product2 Desc2...... 6000 <BINARY> 6
2 Product2 Desc2...... 6000 <BINARY> 7
3 Product3 Desc3...... 5000 <BINARY> 3
4 Product4 Desc4...... 2550 <BINARY> 4
5 Product5 Desc5...... 3000 <BINARY> 5

(5 row(s) affected)
(7 row(s) returned)
@RETURN_VALUE = 0





ALTER PROCEDURE dbo.StoredProcedure2

AS

SELECT
Products.ProductID,
Products.ProductName,
SUBSTRING(Products.Description, 1, 150) + '...' AS Description,
Price.Price,
ProductImage.ProductImage,
ProductImage.ProductImageID
FROM Price INNER JOIN Products ON Price.ProductID =Products.ProductID and (Price.EndDate IS NULL)
INNER JOIN ProductImage ON ProductImage.ProductImageID=Products.ProductID
order by Products.ProductID
RETURN
Output
ProductID ProductName Description Price ProductImage ProductImageID
1 Product1 Desc1...... 1000 <BINARY> 1
2 Product2 Desc2...... 6000 <BINARY> 2
3 Product3 Desc3...... 5000 <BINARY> 3
4 Product4 Desc4...... 2550 <BINARY> 4
5 Product5 Desc5...... 3000 <BINARY> 5

No rows affected.
(5 row(s) returned)
@RETURN_VALUE = 0

[JAFO_IRAN]

سلام

در دومی، موقع join کردن جدول تصاویر عبارت نادرستی بکار رفته:


WRONG:
INNER JOIN ProductImage ON ProductImage.ProductImageID=Products.ProductID
RIGHT :)
INNER JOIN ProductImage ON ProductImage.ProductID=Products.ProductID


اما خودمونیم، خیلی طول کشید تا متوجه شدم - از اون اشتباه‌ها بود!

ارادت