Convert Stored Procedure to Simple T-Sql

Question

I am new to SQL Server and currently learning it. I got following stored procedure which I don't understand.

-- declare a new TABLE variable
DECLARE @Products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description VARCHAR(5000),
Price MONEY,
Image1FileName VARCHAR(50),
Image2FileName VARCHAR(50),
OnDepartmentPromotion bit,
OnCatalogPromotion bit)

-- populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
Product.ProductID, Name,
SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price,
Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product INNER JOIN ProductCategory
ON Product.ProductID = ProductCategory.ProductID
WHERE ProductCategory.CategoryID = @CategoryID
-- return the total number of products using an OUTPUT variable
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products
-- extract the requested page of products
SELECT ProductID, Name, Description, Price, Image1FileName,
Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @Products
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
AND RowNumber <= @PageNumber * @ProductsPerPage

Please! Convert above stored procedure into simple t-sql statements for me so that I get these points. I will really appreciate your work.

Thanks in advance

is this homework? If so, then you should tag it as homework. Also you should show what you have tried to do. — Taryn, Aug 11 '11 at 21:00
No one here is going to do your work for you. If you need help with a specific issue, change your question and let us know that you applied some effort to the problem. Otherwise, you're not likely to get any help at all. — Michael Todd, Aug 11 '11 at 21:18
restore-points according to the tags. But that aside I am reading the current question as: "how to convert a tsql procedure into tsql". That probably is not what the poster is trying to ask — Eddy, Aug 11 '11 at 21:18
@Eddy: Very much like my impression. But in the end it seems a little bit clearer: *'Convert above stored procedure into **simple** t-sql statements…'* So maybe the problem is the script is too difficult for the OP to penetrate? — Andriy M, Aug 12 '11 at 09:49
Maybe but if the OP doesn't feel a need to try to explain his question a bit better I don't feel an urge to spent my time helping him — Eddy, Aug 12 '11 at 10:10
Perhaps if you can't understand what that is doing by reading Books Online (SQL Server help files), you are in the wrong profession. It's really pretty simple stuff. — HLGEM, Aug 12 '11 at 20:02

score 1 · Accepted Answer · answered Aug 11 '11 at 21:18

Something like this would work (I didn't test it):

--extract the requested page of products
SELECT ProductID, Name, Description, Price, Image1FileName,
Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID) AS RowNumber,
Product.ProductID, Name,
SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price,
Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product INNER JOIN ProductCategory
ON Product.ProductID = ProductCategory.ProductID
WHERE ProductCategory.CategoryID = @CategoryID
    ) A
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
AND RowNumber <= @PageNumber * @ProductsPerPage

-- return the total number of products using an OUTPUT variable
SELECT COUNT(ProductID) AS ProductCount FROM Product INNER JOIN ProductCategory
ON Product.ProductID = ProductCategory.ProductID
WHERE ProductCategory.CategoryID = @CategoryID

Convert Stored Procedure to Simple T-Sql

1 Answers1