This article deals with creating a SQL stored procedure for selecting a record.

In each of these stored procedure templates I am declaring a variety of documentation parameters in the header.

I've found these handy in the past when you are working in a team environment, or when you go back to a procedure at a later date. Its much easier to read a simple description in the header, than trawl through the SQL code looking for what it is doing.

So, this declares the procedure name, any parameters and return codes, and also details what it does, and who made it.

In a modified version of this I also hold the SVN revision number here.

The next block of code performs a select on the sysObjects table (part of the Master database). It is checking for the existence of itself. If it finds itself, it will drop the procedure. Note that throughout all of these scripts we are telling the user at each stage what is going on, by printing useful english output back to the screen.

By now we have identified wether or not the procedure previously existed, and if it did, we have dropped it, so we know that we are all good to go. So to create our Insert procedure, we print out a message to the user, then using the "CREATE PROCEDURE" command we create our procedure.

At this point you substitute the "@field" value with your field name, and the [datatype] and (datasize) with the correct values. As an example of this the first line in this proc will create a field named 'varName' with a data type of varchar with a character limit of 100. Just list your fields one after another, seperating with a comma.

After that we create the SQL code, as per usual. We have a Select statement, listing the fields, and the SQL variable declaration (@var). Then we check for any errors, and return a success message if it all worked ok!

By using a script like this I've found that its really simple to have a repeatable standard process that is easy to implement across a team of developers, ensuring you get the same results, no matter who writes the query. It is also very useful if you have a seperate implementation team, as these scripts are re-runnable, they clear up after themselves.

Download the full template here.

This article deals with creating a SQL stored procedure for inserting a record.

In each of these stored procedure templates I am declaring a variety of documentation parameters in the header.

I've found these handy in the past when you are working in a team environment, or when you go back to a procedure at a later date. Its much easier to read a simple description in the header, than trawl through the SQL code looking for what it is doing.

So, this declares the procedure name, any parameters and return codes, and also details what it does, and who made it.

In a modified version of this I also hold the SVN revision number here.

The next block of code performs a select on the sysObjects table (part of the Master database). It is checking for the existence of itself. If it finds itself, it will drop the procedure. Note that throughout all of these scripts we are telling the user at each stage what is going on, by printing useful english output back to the screen.

By now we have identified wether or not the procedure previously existed, and if it did, we have dropped it, so we know that we are all good to go. So to create our Insert procedure, we print out a message to the user, then using the "CREATE PROCEDURE" command we create our procedure.

At this point you substitute the "@field" value with your field name, and the [datatype] and (datasize) with the correct values. As an example of this the first line in this proc will create a field named 'varName' with a data type of varchar with a character limit of 100. Just list your fields one after another, seperating with a comma.

After that we create the SQL code, as per usual. We have an Insert statement, listing the fields, and the SQL variable declaration (@var). Then we check for any errors, and return a success message if it all worked ok!

By using a script like this I've found that its really simple to have a repeatable standard process that is easy to implement across a team of developers, ensuring you get the same results, no matter who writes the query. It is also very useful if you have a seperate implementation team, as these scripts are re-runnable, they clear up after themselves.

Download the full template here.

Over the course of working with one employer, I headed up an investigation into our current server performance, and how would could potentially aim for at least a 25% to 30% increase on our current user thresholds before our capacity maxed out.

After looking through a series of server logs, and data transactions it was easy to see that the server technology being used, Coldfusion, is not the most efficient data handler in the world.

So after stripping back our application layer, taking a peek under the hood, and changing around 600 or so instances of data connectivity from inline SQL code, to Stored Procedures, we had more than exceeded our target gain. In fact under stress testing we had achieved a consistent increase of around 65% threshold. As a quick set of figures, the server loads had changed from CF, running at 75%-80% dropping to 20%-25%, and SQL server running at 6% increased to around 15%.

Based on these figures, and architectural methodologies I've learnt since, I would always recommend stored procedures over inline SQL code. This has lead me to develop a standard set of SQL templates for developers to use.

They are self checking, repeatable SQL templates that will action the desired changes, and check for any existing conditions up front. The idea is that a non technical resource could run them, and receive a useable English response.

1. Select statement
2. Update Statement
3. Insert statement
4. Delete statement