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
- 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.
- Advantages: https://stackoverflow.com/a/22961894/4802664
- Disadvantages: https://stackoverflow.com/a/459531/4802664
Creating stored procedure in SSMS
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
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
DECLARE @result int; EXEC @result = fooProcedure; PRINT @result
CREATE PROCEDURE [dbo].[test] @Name VARCHAR(100), @ID INT OUTPUT AS BEGIN SELECT @ID = UserID from FooDB where Name = @Name Return; END
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
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
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'