Guidelines for writing TSQL scripts

Documentation:Developers

HOME

SPACES

Location: Dashboard > DOCDEV > Pages > Guidelines for writing TSQL scripts

Quick Search:

Guidelines for writing TSQL scripts

Last changed on mar 14, 2006 by Rahul Singh

Naming conventions

All Rainbow procedures are prefixed with "rb_". Eg. rb_Announcements
All staging tables are Postfixed with "_st". Eg. rb_Announcements_st

Release note

Increment the release number in AssemblyInfo.cs

Open "Setup\Scripts\History.xml" and add a new entry:

<Release>
            <Id>1608</Id>
            <Version>1.2.8.1608</Version>
            <Script>1.2.8.1608.sql</Script>
            <Date>2003-04-08T00:00:00.0000000+02:00</Date>
      </Release>

Create the script on "Setup\Scripts" folder
Add on top of file the db version update (date in MM/DDYYYY)
---------------------
--1.2.8.1608.sql
---------------------

INSERT INTO [rb_Versions] ([Release],[Version],[ReleaseDate]) VALUES('1608','1.2.8.1608', CONVERT(datetime, '04/08/2003', 101))
GO

New auto update splits scripts on GO keyword and execute it as command…
Some sql enterprise scripts need slights modifications
1) Remove dbo from rainbow Tables and procedures. Must be retained for system tables and not rainbow tables/procedures in general

NO	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddMessage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [AddMessage]
YES	if exists (select * from dbo.sysobjects where id = object_id(N'[AddMessage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [AddMessage]	2) Remove all COLLATE specification and use in any case the db default 3) Do not use any explicit reference to Rainbow database
NO	Use [Rainbow]	4) Do not use any explicit reference to dbo user
NO	INSERT INTO #TMPResults EXEC rb_GetRelatedTables 'Modules', 'dbo'
YES	INSERT INTO #TMPResults EXEC rb_GetRelatedTables 'Modules'	5) Always check before do changes
NO	drop procedure [AddLink] GO
YES	if exists (select * from dbo.sysobjects where id = object_id(N'[AddLink]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [AddLink] GO	6) If you drop and recreate with a different name double check the existence for both versions
NO	drop procedure [AddLink] GO
YES	if exists (select * from dbo.sysobjects where id = object_id(N'[AddLink]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [AddLink] GO	7) Be sure transactions are not across GO commands:
NO	YES
BEGIN TRANSACTION … GO COMMIT
BEGIN TRANSACTION … COMMIT GO
8) Never change explicitly the current user 9) Keep comments before GO commands
NO	UPDATE rblang_Language SET [en] = @Translation WHERE ([Key] = @Key) AND ([en] = '' or [en] IS NULL) GO --end localization
YES	UPDATE rblang_Language SET [en] = @Translation WHERE ([Key] = @Key) AND ([en] = '' or [en] IS NULL) --end localization GO	10) Keep commands on the same GO
NO	SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO

ALTER PROCEDURE UpdateTab|

YES	SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON ALTER PROCEDURE UpdateTab

0 comments

Page Operations

Space Summary

Naming conventions

Release note

Script conventions