A stored procedure in Microsoft’s SQL Server Management Studio (SSMS) is really just like a subroutine in other programming languages. You can create several SQL statements and put them into a stored procedure.

To save time and memory, extensive or complex processing that requires execution of several SQL statements can be saved into stored procedures.

Regarding stored procedures generally, Wikipedia says: “Stored Procedures may return result sets, i.e., the results of a SELECT statement. Such result sets can be processed using cursors, by other stored procedures, by associating a result-set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. Stored-procedure flow-control statements typically include IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared.”

At MSDN, Microsoft gives the steps to create a stored procedure. We have simplified the steps

1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.
2. Expand Databases, expand the AdventureWorks2012 database, and then expand Programmability.
3. Right-click Stored Procedures, and then click New Stored Procedure.
4. On the Query menu, click Specify Values for Template Parameters.
5. In the Specify Values for Template Parameters dialog box, enter the procedure name.
6. Click OK.
7. In the Query Editor, replace the SELECT statement with the following statement: SELECT * FROM dbo.SimpleBulkInsert
8. To test the syntax, on the Query menu, click Parse. If an error message is returned, compare the statements with the information above and correct as needed.
9. To create the procedure, from the Query menu, click Execute. The procedure is created as an object in the database.
10. To see the procedure listed in Object Explorer, right-click Stored Procedures and select Refresh.
11. To run the procedure, in Object Explorer, right-click the stored procedure name SelectSimpleBulkInsert and select Execute Stored Procedure.

In our example we are not using the AdventureWorks2012 database, but we are using our own database from the previous post that we called myDatabase.

Here is what the code looks like after I made some modifications. I deleted the comments at the top, entered a short description, deleted the parameters, entered the stored procedure name and entered the SELECT statement.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	Just run SELECT statement for all records against table
-- =============================================
CREATE PROCEDURE SelectSimpleBulkInsert 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM dbo.SimpleBulkInsert
END
GO