General
The following should be taken into account when writing stored procedures:
- Stored procedure names should always begin with “spLocal_”. This identifies them to the Proficy Administrator as local custom stored procedures. By default, the Administrator searches for stored procedures starting with the “spLocal_” prefix when the stored procedure option is clicked in the Calculation or Event Model Properties Configuration dialog box.
- Never start a stored procedure name with ‘sp_’ as it tells the SQL Server to look for it in the master database first before the local database so there is a slight performance hit.
- All Transact-SQL reserved words (for example, SELECT, FROM, WHERE, DECLARE, IF, ELSE, BEGIN, END, and so on) should be upper case.
- All SQL data types (for example, int, float, datetime, and so on) should be lower
case and declared variables and their data types should be listed vertically and tab
indented. For example,
DECLARE @Condition int, @Action int, @Value1 float, @Value2 datetime, @Value3 varchar(25)
- All SQL functions (for example, datediff, ltrim, nullif, and so on) should be lower case.
- Variables should not contain any underscores (for example, @MyNewVariable vs @My_New_Variable).
- Every permanent object referenced in a stored procedure should have “dbo.” in front
of it, including the declaration of the stored procedure itself. This is essential
to prevent unnecessary recompiles of the stored procedure. For example,
CREATEPROCEDURE dbo.spLocal_MyStoredProcedure
. - All temporary tables should be created together at the beginning of the stored procedure and then collectively dropped at the end of the stored procedure. This prevents multiple recompiles within the stored procedure.
- When simultaneously assigning multiple values to multiple variables, a single SELECT
statement should be used instead of multiple SET or SELECT statements because there
is a relatively significant performance advantage. For example,
SELECT @MyVariable1 = 5, @MyVariable2 = 6, @MyVariable3 = 7
However, when assigning a single value to a single variable there is marginal performance advantage to using the SET statement and it is currently the recommended approach by Microsoft. For example,
SET @MyVariable1 = 5
. - The default tab size in the SQL editor should be set to 4 characters.