Author Topic: Good Practice To Create Stored Procedure in Database  (Read 2793 times)

BhushanShah

  • Global Moderator
  • Jr. Member
  • *****
  • Posts: 73
  • BhushanShah21
    • View Profile
Good Practice To Create Stored Procedure in Database
« on: September 14, 2012, 10:17:22 AM »
Here is a sample template to create standard stored procedure in database :

Code: [Select]
CREATE PROCEDURE [SchemaName].[StoredProcedureName]
   /*
      Define Parameters
   */
AS
BEGIN   
   SET NOCOUNT ON;
   BEGIN TRY
      BEGIN TRAN
         /*
            Insert your Stored Procedure Code.
         */
      COMMIT TRAN
   END TRY
   BEGIN CATCH         
      ROLLBACK TRANSACTION
     
      /*
         Insert code to Log Error Details.
      */
     
      DECLARE @err VARCHAR(MAX)
      SELECT @err = ERROR_MESSAGE()
      RAISERROR('Error saving : [SchemaName].[StoredProcedureName]', 16, 1, @err)
   END CATCH
END
Regards,
Bhushan Shah

Ritesh Shah

  • Administrator
  • Jr. Member
  • *****
  • Posts: 57
  • Ritesh Shah
    • View Profile
    • IT Technology Blog
Re: Good Practice To Create Stored Procedure in Database
« Reply #1 on: September 14, 2012, 11:19:07 AM »
Bhushan,

This is really a good one. Actually it would be better, if we set it as a default template for Stored Procedure and use it for every new SP we will create.

You can find "Template Explorer" in "View" menu or you can even use short key "Ctrl+Alt+T"
Ritesh Shah
Administrator
Extreme-Advice.com
SQLHub.com

BhushanShah

  • Global Moderator
  • Jr. Member
  • *****
  • Posts: 73
  • BhushanShah21
    • View Profile
Re: Good Practice To Create Stored Procedure in Database
« Reply #2 on: September 14, 2012, 11:52:26 AM »
Sir,

Thanks a lot for that wonderful tip.

But how can we make new template as default template while creating a stored procedure?

As par my knowledge you can change code in  Create Stored Procedure (New Menu).sql but it may loss during any service pack or sql server upgrades.
Regards,
Bhushan Shah