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 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.