Home
>
Archive
>
SSW Standards
>
DeveloperSQLServer
>
SSW SQL Stored Procedure Formatting Standard
There was an error displaying the testimonials. Please
report this error
to SSW and include the following text:
- A network-related or instance-specific error occurred while
establishing a connection to SQL Server. The server was not found or
was not accessible. Verify that the instance name is correct and
that SQL Server is configured to allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server)
SQL Server Stored Procedure Formatting Standard
This standard outlines the standard on formatting Stored Procedures
within SQL Server. Use these standards when creating new Stored
Procedures or if you find an older Stored Procedure that doesn't
follow these standards within SSW.
Note: Some developers may question the convention for naming stored
proc parameters. We agree that this one is certainly open to debate.
You need to ask yourself, "how will using this convention help my
programmers?" and be prepared to drop the convention if the answer is
"well actually it probably won't".
We have always found control naming conventions useful on Access, VB
and ASP forms. Therefore we bring them through to SQL Server - you may
not. For example there is some question on the usefulness on using a
str prefix on string variable types for Char, nChar, Varchar,
nVarchar, Text, nText - in this case you really need to know more
information than that (ie. the length), however you can at least tell
whether you need single quotes in a WHERE clause (ie. WHERE ClientID='
@strClientID '). Overall we believe this convention saves time because
the datatypes are intuitive by their name.
Do you agree with them all? Are we missing some?
Let us know
what you think.
Parameters
- Each parameter is to go on a new line.
-
A prefix of 'p' if the variable is passed in as a parameter i.e.
@plngClientID
-
A three-letter string that indicates the type of variable being used
Parameter Type String Example
|
Parameter Type
|
String
|
Example
|
We Don't use!
|
| Char, nChar, Varchar, nVarchar, Text, nText |
str |
@strEmail varchar(150) |
Chr chv chrn chvn txt txtn |
| Datetime, SmallDatetime |
dte |
@dteDateCreated datetime |
dtm dts |
| Tinyint, Smallint, Integer, Bigint |
int |
@intContactID int |
iny ins inb |
| Bit |
bit |
@bitPrimaryContact bit |
|
| Real |
sng |
@sngTotal real |
rea |
| Float |
dbl |
@dblLength float |
flt |
| Decimal |
dec |
@decLength Decimal(4,2) |
|
| SmallMoney, Money |
cur |
@curTotal money |
mny mns |
| Binary, varBinary |
bin |
@binContract binary |
biv |
| Image |
img |
@imgLogo image |
|
| Timestamp |
tsp |
@tspCurrent timestamp |
|
| UniqueIdentifier |
guid |
@guidOrderID Uniqueidentifier |
|
| Sql_Variant |
var |
@varPrice sql_Variant |
|
| Cursor |
crs |
@crsInventory Cursor |
|
| Table |
tab |
@tabMyTable table ( entryid int not null , lname varchar(16) not
null )
|
|
Example
-- Description Call this proc for every Bounced Email
-- Date v1 11/11/1999 AC
-- Modified v2 14/11/1999 DDK
-- Modified v3 13/12/1999 DDK
CREATE PROC procClientBouncedEmailUpdate
@pstrClientID nvarchar(6),
@pintContactID int,
@pstrBouncedEmail varchar(50)
AS
-- Continued below...
New Lines
-
Start a new line before each keyword ie. SELECT, FROM, WHERE, ORDER
BY.
-
Each field in a SELECT/UPDATE statement is to go on a new line
- Each item is to be indented. (See below)
- Place each condition of a WHERE clause on a new line.
Example
-
UPDATE ClientContact SET DateBouncedLast = getdate(), BouncedTimes
= 1, BouncedEmail = @pstrBouncedEmail FROM ClientContact WHERE
Email = @pstrBouncedEmail AND ContactID = @pintContactID
RETURN
- Figure: Example of bad format
-
UPDATE ClientContact
SET DateBouncedLast = getdate(),
BouncedTimes = 1,
BouncedEmail = @pstrBouncedEmail
FROM ClientContact
WHERE Email = @pstrBouncedEmail
AND ContactID = @pintContactID
RETURN
- Figure: Example of good format
Acknowledgments
Adam Cogan