There are many articles on the internet that cover code style conventions for a wide range of programming languages. I think that it is important for a developer to put an accent on how the code looks like. A good stored procedure is:
- easy to read and understand by yourself or by other developers
- easy to mantain
- easy to extend
- parts of the procedure can be reused in other procedures
So I started asking myself “How can I write an elegant stored procedure?”. I saw different approaches coming from different developers and read some articles on the internet about code conventions in SQL Server. I compared these ideas and I will present you some tips about how I write a stored procedure.
Name: my_procedure
Version: 1.0.2
Author: Andrei C.
Created 13 sept 2012
Description: This procedure is just a dummy stored procedure to show you
the code convetions
the code convetions
Code segments:
1. Declare and compute variables
2. Compute some random values
3. Return the result set
Code sample:
exec my_procedure 10
History:
Vers. Date Who Description
---------------------------------------------------------------------------
---------------------------------------------------------------------------
1.0.0 13 sept 2012 Andrei C. Procedure created
1.0.1 20 aug 2013 George W. Added validation condition to date key value
1.0.2 03 sept 2013 Andrei C. Reviewed how the random values are computed
**************************************************************************/
create procedure my_procedure
@size int
as
begin -- 1. Declare and compute variables
declare @sql nvarchar(max),
@i int
set @sql = ''
if @size < 0
begin
print 'The size is lower than 0'
return
end
end
begin -- 2. Compute some random values
set @i = 0
set @sql = ''
while @i < @size
begin
set @sql += @sql + char(cast((90 - 65 )*rand() + 65 as integer))
set @i = @i + 1
end
end
begin -- 3. Return the result set
select @sql
end
1. Define a convention for keywords and stick with it
Some developers write their keywords with uppercase (“SELECT”, “BEGIN”, “END”), some write them with lower case (“select”, “begin”, “end”), some like to upper the first letter (“Select”) and some use other variations. The recommendation is to use all keywords in caps, but I don’t think that is mandatory. But, I do believe that it is important to define your own way and stick to it. That is, if you decide to use lowercase (like I do), you must commit to your decision and write those keywords in lowercase everywhere.
2. Keep a nice clean header for every procedure
It is important to keep headers for procedures. Whether you are using the predefined model or you define your own (like I did), headers greatly improve readability. I like to keep in the header the name of the procedure, the current version, the author and the creation date. Then, I add a short description about what my procedure does. Try to keep it as brief as possible (2-3 lines tops). Then, the code sample is handy during development and it helps other developers to quickly use your procedure. Finally, although now there are versioning systems for DB also (like Red Gate), I like to keep the versioning as comments in the header too for fast tracking of changes.
3. Define a convention for line spacing
Here there is a big question among SQL developers whether to use tab for line alignment or 2 white spaces. I for one like to use 2 white spaces, but like the case of keywords I think that any of these will do. In fact, any convention is good as long you stick to it.
4. Split your procedure into segments and delimit them by begin and end
The functionality of a stored procedure can usually be split in code zones. In order to separate these zones I use “begin…end” code blocks. These zones can be beautifully collapsed. So if you work on code segment 2, you can let segments 1 and 2 minimized (see picture). Also, you may note that using Ctrl+M+O (does not work in Management Studio 2012) you can collapse all these code zones. However, how to split a procedure in code zones is beyond the scope of this article.
5. Comment your code
Along with the description that lies in the header, you might want to add in-line text comments. I heard a rule that for every line of code you have to write 7 lines of documentation. Of course, this rule is exaggerated and it encourages writing tones of meaningless documentation. Keep your comments simple and explicit. As a rule of thumb for writing comments think about this: “If I had to read the code would I understand what it does?”
Many credits to my co-workers from which I learned a lot of these ideas. Thank you!
