Author : HASSAN MD TAREQ

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.

  • Microsoft Doc - Stored Procedures
  • A stored procedure is a set of SQL statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs
  • A stored procedure is a group of SQL statements that has been created and stored in the database
  • A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data
  • A stored procedures will reduce network traffic and increase the performance
  • If we modify a stored procedure all the clients will get the updated stored procedure
  • https://www.sqlservertutorial.net/sql-server-stored-procedures/
    • SQL Server stored procedures are used to group one or more Transact-SQL statements into logical units
    • The stored procedure are stored as named objects in the SQL Server Database Server.
  • (https://www.essentialsql.com/what-is-a-stored-procedure/) Stored procedures can be thought of having three main parts:
    • Inputs: store procedure can accept parameter values as inputs
    • Execution: stored procedures can execute SQL statements / call another stored procedure / manipulate results of SQL queries via cursors
    • Outputs: A stored procedure can return a single value or a result set (set of rows)

Why use stored procedures

  • to simplify the administration and maintenance
  • with stored procedures, our code is stored with the data - code is stored in the database. Which reduces the chances the code will ever be misplaced, and it also provides the advantage of every time the database is backed up, all of my code is backed up
  • Stored procedures can improve our security and simplify the administration of security. Using stored procedures we can easily provide different levels of access to different users. I can prevent any user from directly accessing any table. The only way they can read or modify the data is to utilize my stored procedure. This technique makes it very simple to prevent certain actions
  • performance improvement - stored procedure is a precompiled collection of Transact-SQL statements (Stored procedures are precompiled and cached so the performance is much better)

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

  • the number returned indicates failure of success of execution of stored procedure
  • we can decide to return -1 for failure and 1 for success
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'