To Code is Awesome

The right way to write object creation scripts in Sql Server (create tables, procedures, functions, constraints, etc) is as follows:

1. First, check is the object exists.
• If it does, delete it.
2. Then, create the object
3. Next, repeat for all the objects that depend upon the object we just create

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

[sql]
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

[/sql]
As we can see, the identation is very important for the code to be clear and legible. I like the following format:
[sql]
COMMAND
arguments
[/sql]

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:

1. Functionality: What the procedure does
2. List of parameters, including type and description
3. Usage example

There are other important data to include, as change history, where at least we must specify:

1. Who write the change
2. Why
3. When

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.

[sql]
/* 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
[/sql]

It’s really easy, the important thing is to keep order and structure in the code.