Stored Procedures

So, in our last post – Back to Basics – we went over what a stored procedure was and what goes into it.

Now, let’s show you a simple stored procedure.

CREATE PROCEDURE StoredProcedureTest AS

SELECT 1/1

GO

I know. I know. You thought we were going to begin by building skyscrapers, instead, we’re trying to find cinder blocks.

The CREATE PROCEDURE tells SQL Server that we want to create a stored procedure

Next comes the name – StoredProcedureTest. Now, there are some people that use underscores, some use camel case, just be consistent in the names.

The AS tells SQL Server that coming up is the real meat of the stored procedure – the logic that we need to work with.

The final GO tells SQL Server that we’re done with the logic for our stored procedure.

Now, after all that, you’re wondering to yourself “Why would I create a stored procedure at all?” Well, I’m glad you asked!

One reason I like stored procedures is that the stored procedure (normally) runs on the SQL Server itself instead of on the client box, making it (theoretically) much faster.

The other major reason I like to use stored procedures is so that my blocks of code can be re-used several times now and in the future. Like most people I don’t want to write out the same blocks of code, only changing one or two portions of it each time.

And, we’ll go into more of how we do that next time…