Demonstrates how to create, test and execute an MS SQL Server stored procedure. It also demonstrates the use of input and output parameters, and a return value. (Everything demonstrated is similar for most databases other than SQL Server.)

To an extent it also demonstrates Progressive Testing as I indicate how the code was created and tested in stages.

The TechNet article is old but still a useful first read: "A stored procedure is a group of Transact-SQL statements compiled into a single execution plan."

Note that stored procedures can include programming logic, such as IF statements.

I don't discuss the benefits of SPs, the when and the why. This is obviously something that you should investigate and understand, starting with the above articles.

Start Microsoft SQL Server Management Studio and create a database. I called my Staff3. (You don't have to use Management Studio, LINQPad and PowerShell are alternatives if you are familiar with either.)

CREATE DATABASE Staff3
GO
USE Staff3
GO

Then create a single table named Staff and populate it with some data:

At this point I created a C# Console application named 'StoredProcedure'. All I want to do with it is to prove that I can connect to the database and read 'something' from the Staff table. I did use using statements though, making it easier to extend later on.

The syntax is here, CREATE PROCEDURE (Transact-SQL) (msdn). Notice that I have omitted the optional BEGIN and END keywords. Typically, an SP is created separately from its testing and execution, so BEGIN and END aren't too significant. Use them if you prefer. If you are creating and testing in the same query make sure to follow the definition with GO.

You could name your SPs with a prefix such as spProcedureName, but don't use sp_ as this is used by MS for system SPs.

You can use EXEC or EXECUTE (and PROC or PROCEDURE).

If you are creating and testing in the same window then this provides a basic outline for replacing the SP:

DROP PROCEDURE OfficeStats
GO
CREATE PROCEDURE OfficeStats
AS
GO

You may want to investigate checking if the SP exists before dropping it. This SO topic discusses checking if a table exists with code like IF OBJECT_ID('*objectName*', 'U') IS NOT NULL. I suspect that similar code will work for an SP but I didn't pursue this, although it looks like 'P' replaces 'U' for a stored procedure, sys.objects (Transact-SQL).

(The deletion and re-creation is only appropriate during development, an SP will most often be a persistent object in the database.)

I prefer to put the AS keyword on a separate line, as I commonly forget to include it if at the end of the first line.

Run the above code and it should display 6 rows for the London (LDN) staff. We aren't using parameters and the result of executing the SP is the output from the first SELECT statement. (If there is more than one SELECT then executing an SP in Management Studio will display both resultsets.)

Now we can add an input and an output parameter. We will input an OfficeID code ('LDN') and obtain the list of this office's staff, also outputting the average of their salaries.