What are Stored Procedures in SQL Server 2012

Stored procedures are list of commands that performs a certain tasks. This allows database administrators to run set of TransactSQL commands. Just like scripts, they can be written to accept parameters and arguments. You may input and return data just like scripts. Since stored procedures are run as a whole, this save network bandwidth instead of running single commands. Stored procedures are perfect when you have to perform set of tasks more than once. This allows you to reuse the same set of stored procedures instead of running set of commands manually. This allow decreases mistakes and keeps the environment consistent. There is various types of stored procedures, System, temporary and User-definded procedures. System stored procedures are built-in to SQL 2012 and located in hidden resource database. User defined databases can be user defined stored procedures.

Creating a Stored Procedure (using SQL Management Studio)

Open SQL Management Studio under SQL Server 2012
Under Databases, expand the database, then “Programmability”
Rightclick stored procedures and click New stored procedure.
Enter Author, Create Date, Description, Procedure name
Follow on screen instructions.

To create a stored procedure using TransactSQL use below code:

USE EMPLOYEEDATABASE;
GO
CREATE PROCEDURE HR.uspGetEmployeesTest2
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS

SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HR.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName
AND EndDate IS NULL;

GO