The right way to write object creation scripts in Sql Server (create tables, procedures, functions, constraints, etc) is as follows:
- First, check is the object exists.
- If it does, delete it.
**Important** Always write “USE Database GO” at the beginning of the script.
**Importante** Always include descriptive comment about the objects, always write your comments as if you were explaining to a kid, make them clear and short.
Let’s see an example, table creation script.
USE MyDataBase GO /* Object: MyTable */ -- If exists, delete it IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MyTable' ) BEGIN DROP TABLE MiTabla END GO /* * MyTable * - Test table, stores testing data * Include relevant information as author, date, project name, reason for creation, etc. */ CREATE TABLE MyTable( MyTableId int NOT NULL, Description varchar(30) NOT NULL, ) GO /** * PK_MiTabla * - Primary key constraint */ ALTER TABLE MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY ( MyTableId ) GO
As we can see, the identation is very important for the code to be clear and legible. I like the following format:
With this format I can read the instructions vertically with ease. Now will see an example for a stored procedure. It is really important to document the following:
- Functionality: What the procedure does
- List of parameters, including type and description
- Usage example
There are other important data to include, as change history, where at least we must specify:
- Who write the change
Write our scripts this way, in case we want to run them in another server, they will not cause conflict if they run twice by mistake, and if the execution is made for somebody else, it will very clear for the other person know what is doing and why.
/* Object: myStoredProcedure */ -- If exists, delete it IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'myStoredProcedure' AND ROUTINE_TYPE = 'PROCEDURE' ) BEGIN DROP PROCEDURE myStoredProcedure END GO /* myStoredProcedure: - Select all records of my table using the key @myParameter Parameters: @myParameter INT - The table id Usage: EXEC myStoredProcedure 1001 // Other relevant information Luis Espino, March 27, 2016, procedure creation - Example of creation script for the blog */ CREATE PROCEDURE myStoredProcedure ( @myParameter INT ) AS BEGIN -- Logic brief description -- Select the records using the parameters SELECT * FROM MiTabla WHERE MiTablaId = @miParametro END GO -- Grant permissions to users that can use the procedure, public in this case GRANT EXECUTE ON myStoredProcedure TO PUBLIC GO
It’s really easy, the important thing is to keep order and structure in the code.