PL/SQL Best Practices

USE{database name}  

IF OBJECT_ID('{owner}.{procedure name}', 'IsPRocedure') IS NOT NULL
BEGIN
DROP PROCEDURE {owner}.{procedure name};
END
GO

CREATE PROCEDURE {owner}.{procedure name}  
      [{parameter}  {data type}]  
as  
/*******************************************************************  
* PROCEDURE: {procedure name}  
* PURPOSE: {brief procedure description}  
* NOTES: {special set up or requirements, etc.}  
* CREATED:  {developer name} {date}  
* MODIFIED   
* DATE            AUTHOR                  DESCRIPTION  
*-------------------------------------------------------------------  
* {date}          {developer} {brief modification description}  
*******************************************************************/  
DECLARE {variable name} {data type};
-- Add more variables as needed
SET {session variables}
-- Add more session variables as needed
{initialize variables};
{body of procedure};
RETURN;
EXCEPTION
WHEN {error type} THEN
{error handler};
END;
GO

set @Example = 'Bills example'

if((select 1 where 1 = 2) isnot null)

IF EXISTS (SELECT 1 
           FROM dbo.TB_Location 
           WHERE Type = 50)  
BEGIN  
IF ((SELECT COUNT(Id) FROM dbo.TB_Location WHERE Type = 50) > 0) 
END

SELECT @identity AS ExamId,   
(@pointsReceived/ @pTotalPoints)AS Average 

SELECT @identity AS UserId   
--is preferred over   
SELECT @identity AS Id  

SELECT 
    t.TaskId
FROM Task.dbo.TASK t
INNER JOIN Task.dbo.ENROLLMENT et
    ON t.TaskId = et.TaskId
WHERE et.MemberId = @pMemberId
    AND (
        (t.Due_DT <= @pStartDate)
        OR (t.DueDaTe >= @pEndDate)
        OR (et.FLAG = 1)
    )

INSERT INTO SUBSCRIBE
EXECUTE SUBSCRIBERS_BUILDNEW_SYSTEM;

INSERT INTO [dbo].[TB_Decision]
(
    [Id],
    [TestId],
    [qid],
    [DecisionId],
    [Comments]
)
VALUES
(
    1,
    1234,
    253535,
    1,
    'hello'
);
GO

Provide an inline comment to explain any hard-coded value.

Updates

Use the following outline for simple update statements. Format the where clause as described earlier.
Example,

UPDATE [dbo].[TB_Decision]
SET 
    [TestId] = 12343,
    [qid] = 111,
    [DecisionId] = 111,
    [Comments] = 111
WHERE 
    [Id] = 1;

Deletes

Use the following outline for simple delete statements. Format the where clause as described earlier.

Example,

DELETE FROM [dbo].[TB_Decision]
WHERE Id = 1;

Transactions

If a transaction is necessary for a multi-statement operation, and an OLEDB client connection will not manage the code, use,

BEGIN TRANSACTION [{transaction name}]

{statements}

IF {error}
BEGIN
    ROLLBACK TRANSACTION [{transaction name}];
END;
ELSE
BEGIN
    COMMIT TRANSACTION [{transaction name}];
END;

Transact-SQL Flow-of-control statements

Use the following outlines for if statements,

IF ({condition})
BEGIN
    {statement}
END;
ELSE IF ({condition})
BEGIN
    {statement}
END;
ELSE
BEGIN
    {statement}
END;

Use the following outlines for a while statements,

WHILE ({condition})
BEGIN
    {statement}
END;

Use the following outlines for case statements. (Note that the SQL case construct is not a

SELECT 
    CASE [{column or variable}]
        WHEN {value | expression} THEN {result if this value}
        [WHEN {value | expression} THEN {result if this value}]
        [ELSE {default result}]
    END

Cursors

• Use cursors only where a set-based operation is inappropriate. (Rarely)
• Never use a cursor to return data to the application. The performance hit for this is unacceptable.

Before Executing Scripts, please make sure of the following,

• Make sure all the scripts are Re-Runnable.
• All data update scripts need to be checked carefully for not using any identity columns directly, as they can be different in different versions of the databases.
• dbo.XXXX or XXXX (where XXXX is the object name) should be used explicitly in the script to avoid creating objects with the person's userid.
• Only use ALTER statements when adding columns, removing columns, changing foreign key constraints, changing default columns, etc.
• Never use ALTER statements for Views, Functions, and Stored Procedures.
• When creating any database object, check for its existence first. If objects exist, drop them and then finally create them.
• While adding columns, foreign keys, and primary keys to the table, always check for their existence first.
• Add a USE statement at the beginning of every script.
• Filenames should not create any spaces. Use underscores to separate names if necessary.
• Every script should end with GO.

Some examples of Re-runnable scripts

Create table script,

Below SQL statement is to create the table. If the table exists, then drop and create a new table.

IF OBJECT_ID('dbo.Types') IS NOT NULL
BEGIN
    DROP TABLE Types;
END;
GO

CREATE TABLE [dbo].[Types](
    [uid] INT IDENTITY(1, 1) NOT NULL,
    [DocumentDescription] NVARCHAR(200) NOT NULL,
    [QuestionID] VARCHAR(20) NOT NULL
);
GO

Below the SQL statement is Alter table script to add a new column,

IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'Questions'
    AND COLUMN_NAME = 'IsMandatory'
)
BEGIN
    ALTER TABLE [dbo].[Questions]
    ADD IsMandatory BIT NULL;
END;
GO

Below the SQL statement is Alter table script to drop a column,

IF EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'Elements'
    AND COLUMN_NAME = 'Abbreviation'
)
BEGIN
    ALTER TABLE [dbo].[Elements]
    DROP COLUMN [Abbreviation];
END;
GO

Below the SQL statement are Alter table scripts to add primary keys and foreign keys,

IF OBJECT_ID('PK_Info') IS NULL
BEGIN
    ALTER TABLE [dbo].[Info]
    ADD CONSTRAINT [PK_Info]
    PRIMARY KEY CLUSTERED ([uid] ASC);
END;
GO

IF NOT EXISTS (
    SELECT 1
    FROM sys.foreign_keys
    WHERE name = 'FK_Payments_Type'
)
BEGIN
    ALTER TABLE [dbo].[Payments]
    ADD CONSTRAINT [FK_Payments_Type]
    FOREIGN KEY ([TypeID]) REFERENCES [dbo].[Type]([TypeID])
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;
END;
GO

Below the SQL statement is Alter table script to add the Default constraint,

ALTER TABLE [dbo].[QA]
ADD CONSTRAINT [DF_QA_Type_2]
DEFAULT 'C' FOR [Type];
GO

Below the SQL statement is Alter table script to Drop a constraint,

IF EXISTS (
    SELECT * FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'[DF__QA__Type__2]')
    AND type = 'DA'
)
BEGIN
    ALTER TABLE [dbo].[QA]
    DROP CONSTRAINT [DF__QA__Type__2];
END;
GO

Below SQL statement is Creating/ Altering Stored Procedures,

IF EXISTS (
    SELECT * FROM sys.objects
    WHERE type = 'P'
    AND name = 'AddRequest'
)
BEGIN
    DROP PROCEDURE [dbo].[AddRequest];
END;
GO

CREATE PROCEDURE [dbo].[AddRequest]
AS
BEGIN  
   -- add the body of the procedure here
END;
GO

Below the SQL statement is the data update script to Insert records,

IF NOT EXISTS (
    SELECT * FROM [TYPE]
    WHERE TYPEID = 'RESUBMIT'
)
BEGIN
    INSERT INTO [TYPE](TYPEID)
    VALUES('RESUBMIT');
END;

Below the SQL statement is the data update script to Delete records,

IF EXISTS (
    SELECT * FROM TYPE
    WHERE TYPEID = 'RESUBMIT'
)
BEGIN
    DELETE FROM TYPE
    WHERE TYPEID='RESUBMIT'
END;