Another small nugget that I think will help people grow and be more productive in their database design. I am a fan of stored procedures and functions when developing software solutions. I like my actual CRUD methods for implementation at the database level. This allows me to balance my work between the application software (business logic and data access) and the database itself. Not wanting to start a religious war, but I want people to quickly develop stored procedures and using best practices through templates.
Let's start by creating our own templates in SQL Server 2005 Management Studio. First, you need to show the Template Explorer in Studio.
alt text http://www.cloudsocket.com/images/image-thumb10.png
This will show the following:
alt text http://www.cloudsocket.com/images/image-thumb11.png
alt text http://www.cloudsocket.com/images/image-thumb12.png
alt text http://www.cloudsocket.com/images/image-thumb13.png
An empty template is created in the IDE. To change a template, right-click on the template and select "Change." You will receive a blank request window in the IDE. Now you can insert your template implementation. I have a new stored procedure template for enabling TRY CATCH. I like to include error handling in my stored procedures. With the new TRY CATCH addition to TSQL in SQL Server 2005, we should try to use this powerful exception handling mechanism through our code, including database code. Save the template, and you are all ready to use the new template to create stored procedures.
-- ====================================================== -- Create basic stored procedure template with TRY CATCH -- ====================================================== SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN TRY BEGIN TRANSACTION -- Start the transaction SELECT @p1, @p2 -- If we reach here, success! COMMIT END TRY BEGIN CATCH -- there was an error IF @@TRANCOUNT > 0 ROLLBACK -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH GO