Close Menu

SQL-SERVER

 

Scripting Rules



Though there are no standards and rules defined by anyone for writing scripts, Some organizations and individuals follow their own way of writing scripts, while Some corporate organizations have the rules of writing scripts being defined into their company policies which needs to be followed. Overall there are some basic guidelines that are to be followed while writing a stored procedure or a function or any other type of script.These Scripting rules can be applicable to any kind of scripting scenarios where you write code and are not limited to and may vary depending on the situation however the basics which are described in this does not change. These guidelines or rules will help us in implementation and execution of the scripts in a proper format or manner and also will help to identify the below Attributes.


  1. Copyright Notice
  2. Publish Status
  3. Script name and Type :
  4. Version
  5. Date of Creation
  6. Author
  7. Description
  8. Modification History

Modification History has the below sections
 
Modification History                                                     
                                                                          
 TRN  Date          Name                  Description                                  
 ---  -----------   ----------            ----------------------------------
                                       

Disclaimer : The processes defined in this section may contradict with your organization's policies of Naming conventions and Scripting procedures. Hence Kindly check with your organizations Policies, and Scripting procedures while Writing scripts

There are basically manyways in which you can write a piece of code and get it executed however we need to specifically follows standards of writing scripts. Let us consider a scenario where u were asked to write a script which needs to be executed on a SQL-Server. The script.In this scenario We have to write stored procedure named [uspGetParamVal].
Given below are two different ways of writing the same script and both the scripting techniques work. However both are different while executing. The best way to understand why do we need to follow some basic scrpting techniques can be understood if we compare both of the below posted methods (generic and Standard).
In example below I am showing a Generic way to write a stored procedure


Method 1 Generic


CREATE  PROCEDURE [dbo].[uspGetParamVal]
@GsParm         NVARCHAR(255),
@GsKey          NVARCHAR(255)
AS
BEGIN    
DECLARE
@iSEVERITY          INT ,              
    @iSTATE             INT ,            
    @vcErrorMsg         VARCHAR(255)
SELECT
    @iSEVERITY          = 14,              
    @iSTATE             = 1,              
    @vcErrorMsg         = 'OK'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SET NOCOUNT ON
 BEGIN TRY  
SELECT         
            ISNULL(CHAR_VALUE, ' '),
            ISNULL(NUM_VALUE, 0),
            PERMANENT
    FROM  GEN_PURP_PARMS 
    WHERE    [KEY]  = UPPER(@GsKey)
     AND    PARM  = UPPER(@GsParm)
END TRY
BEGIN CATCH
    IF(@vcErrorMsg <> ERROR_MESSAGE())
    BEGIN        
        SELECT  @vcErrorMsg = 'uspGetParamVal: ' + @vcErrorMsg + ERROR_MESSAGE(),
                @iSEVERITY  = ERROR_SEVERITY(),
               @iSTATE     = ERROR_STATE()            
    END    
    RAISERROR(@vcErrorMsg,@iSEVERITY,@iSTATE)
    RETURN
END CATCH
END 
GO


if you look at and compare both the scenario's we can easily identify that its completely different if you go through the comments and the code you will understand why and what is the right way to write your script



In example below I am showing a Standard way to write a stored procedure.


Method 2 Standard

USE [MSDB1] 
GO
//--A instance generally has more than one database so its a good //
//--idea to select what database your code should be  run on //
 
IF EXISTS (select * from syscomments where id = object_id('dbo. uspGetParamVal') 
and texttype & 4 = 0)
BEGIN
    DROP PROCEDURE dbo.uspGetParamVal
    IF OBJECT_ID('dbo. uspGetParamVal') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.uspGetParamVal >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.uspGetParamVal >>>'
END
//--A previously existing procedure may exist so its a mandatory 
//--to delete the old procedure from the DB//
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
//-- no need to use Quotes everytime we specify//
 
 
CREATE  PROCEDURE [dbo].[uspGetParamVal]
//copyright description//
/************************************************************************ 
 
                         NOTICE OF COPYRIGHT                              
                 Proprietary Information Restriction                      
                                                                          
 This is an unpublished work protected both under trade secret laws as a  
 proprietary work product and under the U.S. Copyright Act of 1976, as    
 amended.  It contains technical information which is proprietary to      
 Raghuveera.com.//-- should be replaced with your company's
 Name// The contents of this document may not be used, reproduced,
  distributed, or disclosed 
 in whole or in part. Unpublished (c)//Is this published t the public
  use or not// 2008 Raghuveera.com.  All rights reserved.     
**************************************************************************
--//Self Explanatory//
 Procedure    :   dbo.uspGetParamVal //--we are writing this procedure as a DataBase object//     
 Version      :   1.00       //-- wich version of the script//
 Created      :   2-APR-2015          //--creation date     //                            
 Author       :   Raghu Veera         //--created by//
 Description  :  Get Values of parameters //--description//                                                        
 Modification History                                                     
                                                                          
 TRN  Date          Name                  Description                                  
 ---  -----------   ----------            ----------------------------------
 001  2-APR-2015    Raghu Veera            Created                                      
          
****************************************************************************/
 
@GsParm         NVARCHAR(255),//-- No idea what this  should be so i gave varchar//
@GsKey          NVARCHAR(255) //--No idea what this  should be so i gave varchar//
AS
BEGIN    //SAVE TRANSACTION uspReadConfigParm//
DECLARE
@iSEVERITY          INT ,              //-- User set severity level      //
    @iSTATE             INT ,              //-- Parameter in RAISERROR//
    @vcErrorMsg         VARCHAR(255)
SELECT
    @iSEVERITY          = 14,              //-- Default user severity level  //
    @iSTATE             = 1,               //-- Default state - DON''T CHANGE //
    @vcErrorMsg         = 'OK'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED //--setting isolation levels//
SET NOCOUNT ON
 
BEGIN TRY  //--Start of the begin try operation//
SELECT         //----Actual stored proc//
            ISNULL(CHAR_VALUE, ' '),
            ISNULL(NUM_VALUE, 0),
            PERMANENT
    FROM  GEN_PURP_PARMS 
    WHERE    [KEY]  = UPPER(@GsKey)
     AND    PARM  = UPPER(@GsParm)
 
 
END TRY
BEGIN CATCH
   
 
    /********************************************/
    /* Throw Error                              */ 
    /********************************************/
    IF(@vcErrorMsg <> ERROR_MESSAGE())
    BEGIN        
        SELECT  @vcErrorMsg = 'uspGetParamVal: ' + @vcErrorMsg + ERROR_MESSAGE(),
                @iSEVERITY  = ERROR_SEVERITY(),
               @iSTATE     = ERROR_STATE()            
    END    
    RAISERROR(@vcErrorMsg,@iSEVERITY,@iSTATE)
    RETURN
END CATCH
 
END
 
 
 
GO
 
 
 

you can download the Script here here