Sample T-SQL stored procedure: P_DisplayProductDetails

Sample T-SQL stored procedure: P_DisplayProductDetails

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,

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.


Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in July 2006

 @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
Back to top