 |
|
|
Home > Sample T-SQL stored procedure: P_DisplayProductDetails |
|
|
|
|
|
|
Sample T-SQL stored procedure: P_DisplayProductDetails |
|
| 06 Jul 2006 | Joe Toscano, Contributor |
|
|
Our sample stored procedure displays product details from the AdventureWorks database using a function to rank the unit price for each product subcategory. The procedure accepts the category name as an optional input parameter. Several output parameters feed useful information back to the calling batch. Finally, the stored procedure uses structured exception handling, as discussed in a previous tip.
Use AdventureWorks
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND
name = 'P_DisplayProductDetails')
DROP Procedure P_DisplayProductDetails
GO
CREATE Procedure P_DisplayProductDetails
(@Categoryname varchar(50) = NULL,
@MatchingRows int = NULL OUTPUT,
@ErrorString varchar(128) = NULL OUTPUT,
@ErrorNumber int = NULL OUTPUT)
as
BEGIN TRY
-- Append a % so our callers don't have to know
exact subcategory names
if @CategoryName is null
select @CategoryName = '%'
else
select @CategoryName = @CategoryName + '%'
-- Use a rank function to rank data by List Price
over subcategory name
-- The DENSE_RANK assigns consecutive rank values
SELECT Production.Product.ProductID,
Production.Product.Name AS ProductName,
Production.ProductCategory.Name AS CategoryName,
Production.ProductSubcategory.Name AS SubcategoryName,
Production.Product.ListPrice,
DENSE_RANK() over (Partition by
Production.ProductSubcategory.Name ORDER BY
Production.Product.ListPrice DESC) as PriceRank
FROM Production.Product
INNER JOIN Production.ProductSubcategory
ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory
ON Production.ProductSubcategory.ProductCategoryID =
Production.ProductCategory.ProductCategoryID
WHERE Production.ProductCategory.Name like @CategoryName
ORDER BY Production.ProductCategory.Name
select @MatchingRows = @@ROWCOUNT
return 0
END TRY
BEGIN CATCH
-- LOG THE ERROR … WE MAY WANT TO SKIP THIS STEP WHILE DEBUGGING !
insert dbo.Application_Error_Log (UserName, errorNumber,
errorSeverity, errorState, errorMessage)
values (suser_sname(), ERROR_NUMBER(),ERROR_SEVERITY(),
ERROR_STATE(), ERROR_MESSAGE())
SELECT @ErrorNumber = ERROR_NUMBER(),
@ErrorString = ERROR_MESSAGE()
RAISERROR (@ErrorString, 16,1)
END CATCH
How to debug T-SQL stored procedures
 Home: Introduction
Part 1: Sample stored procedure
Part 2: Where to start debugging the stored procedure
Part 3: How to step into or run the stored procedure
Part 4: Visual Studio debug windows
| ABOUT THE AUTHOR: |
|
Joe Toscano
About the author: Joe Toscano is a SQL Server instructor, speaker and database consultant for Micro Endeavors Inc. He has worked extensively with SQL Server since version 6.5, as well as Sybase and Informix prior to that. Toscano's areas of expertise include Data Transformation Services (DTS), Integration Services (SSIS), Transact-SQL (T-SQL) programming, data warehousing, performance tuning, replication, high availability (mirroring), security, disaster recovery, upgrading, migration projects and lights-out DBA tasks.
Copyright 2006 TechTarget
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
