SQL Server stored procedures best practices

Today a post about SQL Server naming conventions. I've gathered these naming conventions in the last couple of years and i've implemented them at projects at my customers. This is the fourth post in a serie of best practice posts (part I, part II, part III and part IV ). As said, this post is about naming conventions and these naming conventions can be subdivided in server objects-, database objects- and T-SQL naming conventions.

• Naamconvention according to Camel Casing.
• Never prefix a stored procedure with sp_.
• Think about schemas when you're using a lot prefixing in the names of stored stored procedures.
• Create stored procedures in the database where they're used.
• Stored procedures that are used in OLEDB Sources has the following conventions usp<manipulation>_<name>.
• Use 'par' as a prefix for parameter variables (eg @intParTableName). This will help reading stored procedures and it distinguishes SP variables and parameter variables.
• Write comment if something is not quite clear. Length of comment doesn't effect performance.
• Always write case consistent names in your code. Meaning that when you move your code from Case insensitive database to a case sensitive database it will still work.
• Add a debug parameter to a stored procedure. This could be a bit type. When you pass a 1 all SQL statements are printed and nothing is executed. This way you can debug your stored procedure and it's even more helpfull when you're using dynaminc SQL.
• If you're using multiple times a function in a stored procedure, store the result once in a variable and use this variable instead.
• Make sure that  a stored procedure always return a returnvalue. Return values are standardized and are used for returning the status of the execution of a stored procedure. Use the OUTPUT parameter for returning data.
• Use SET NOCOUNT ON at the beginning of a stored procedure (and triggers) as will messsage be suppressed, like  '(1 row(s) affected)' . this enhance performance and reduces netwerktraffic
• Use a less possible SET statements because they can issue a recompilation of the stored procedure.
• Use a proper length of input parameter variable. Using a to large input parameter variable can cause SQL injection and it reduces memory usage.
• don't use WITH RECOMPILE with your stored procedures
• Keep your stored procedures short. Break long stored procedures in smaller stored procedures.
• Use as less possible "WITH ENCRYPTION". Only use this when end users have access to the database and/or when you're using source control software.

Greetz,
Hennie