fan2005
دوشنبه 12 آذر 1386, 14:04 عصر
با سلام ،
تفاوت این دو 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
تفاوت این دو 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