Author : MD TAREQ HASSAN

What is stored procedure?

A SQL stored procedure (SP) is a collection SQL statements and sql command logic, which is compiled and stored on the database. The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data.

Stored procedures are a way that we can take multiple T-SQL statements and combine them into a single unit of work. They allow us to control access to our database, so rather than allowing users to run any query they want, we can instead force them to run a stored procedure that would run a pre-defined query. It also allows us to do some error checking. Based on the results of a query, we could send back information or send back an error message.

Why use stored procedures

Advantages and disadvantages

There are several benefits to using stored procedures over issuing raw T-SQL commands to the database:

First, they’re more consistent. Every time I ask for customer orders using a stored procedure, I can be assured that the database will process all the tables involved in such a query in exactly the same way. Further, when all the applications that interact with the database utilize the same stored procedure, it makes maintenance much easier. To make a change, you simply update the single stored procedure that they are all referencing, rather than updating every application’s internal logic if they are all making calls to the database on their own.

Second, they’re more secure. The stored procedure does not expose the table structure of a database to the end user, but instead keeps all the table references, joins and column names hidden from view within a procedure that never leaves the server. The only thing that the end user or application receives are the columns, including aliases, that are required to fulfill the request. Also on the security front, because stored procedures are saved on the server as an object, that means that they can have the same security permissions assigned and revoked, just like any table or view object. So managing access to the stored procedure in the first place is as easy as assigning them to the same schemas that you’re already using. Users can be granted access to a stored procedure, but denied access to the underlying tables that they reference as an added level of security.

Finally, stored procedures are simply faster.

Stored procedures do come with downsides, basically the maintenance associated with your basic CRUD operation. 
Let's say for each table you have an Insert, Update, Delete and at least one select based on the primary key, that means each table will have 4 procedures. 
Now take a decent size database of 400 tables, and you have 1600 procedures! And that's assuming you don't have duplicates which you probably will.

Creating stored procedure in SSMS

Step-1

Creating stored procedure in ssms Step 1

Step-2

Creating stored procedure in ssms Step 2

Step-3

Creating stored procedure in ssms Step 3

Step-4

Creating stored procedure in ssms Step 4

More: how-to-create-a-stored-procedure-in-sql-server-management-studio

Creating stored procedure using T-SQL

CREATE PROCEDURE highTemperatureBetweenDates 
@startDate datetime, @endDate datetime 
AS 
	IF (@startDate IS NOT NULL) AND (@endDate IS NOT NULL)
		
		SELECT VehicleRegistration, Temperature, RecordedWhen
		FROM  [Website].[VehicleTemperatures]
		WHERE Temperature > 4.9
		AND RecordedWhen >= @startDate
		AND RecordedWhen <= @endDate

	ELSE
		SELECT 'Invalid Date'
GO

More: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure

Returning data set and numeral

CREATE PROC fooProcedure
AS
BEGIN
	-- returning multiple data (data set), all select results will be in output if fooProcedure is called
	SELECT 'Data 1'
	SELECT 'Data 2'
	... ... ... ...
	
	RETURN 1  -- 1 indicates fooProcedure was executed successfully, we can return -1 to indicate error
END

GO

Usage

DECLARE @result int; 
EXEC @result = fooProcedure;
PRINT @result

Output parameter

https://stackoverflow.com/a/35453985/4802664

CREATE PROCEDURE [dbo].[test] 
@Name VARCHAR(100), @ID INT OUTPUT   
AS
BEGIN   
	SELECT @ID = UserID from FooDB where  Name = @Name   
	Return;
END

Usage

DECLARE @ID INT    
EXEC [dbo].[test] 'Bar', @ID OUTPUT   
PRINT @ID

Returning data using cursors

CREATE PROC procedureCursor(@authors CURSOR VARYING OUTPUT)
AS 
BEGIN
	SET @authors = CURSOR FOR
	
	SELECT firstname
	FROM authors
	ORDER BY firstname

	OPEN @authors
END

GO

Usage

DECLARE @myCursor CURSOR
DECLARE @firstName varchar(50)

EXEC dbo.procedureCursor @authors = @myCursor OUTPUT

FETCH NEXT FROM @myCursor INTO @firstName
SELECT @firstName

CLOSE @myCursor
DEALLOCATE @myCursor

See: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure#examples-of-cursor-output-parameters

Transaction in stored procedure

CREATE PROC deleteAuthor (@first varchar(50), @last varchar(50))
AS
BEGIN
	BEGIN TRANSACTION
	 
	 UPDATE AUTHORS 
	 SET Active = 0
	 WHERE firstName = @first AND lastName = @last

	 UPDATE AUTHORS 
	 SET PhoneNumber = NULL
	 WHERE firstName = @first AND lastName = @last

	 IF @@ERROR != 0
		ROLLBACK TRANSACTION
		ELSE
			COMMIT TRANSACTION
END

GO

EXEC deleteAuthor  'John', 'Doe'