You can keep the database-level code that you need to use every time in the application through “stored procedures” in the database.
You can achieve the same result by repeatedly sending the same code within the application. But SPs will be faster.
Because SPs are only compiled the first time they are run and receive an execution plan.
You can find the details of the execution plan in the article titled “What is Execution Plan On SQL Server“.
They will use this execution plan in their next work.
They will perform better because they will work without compilation.
But each time running with the same execution plan can sometimes lead to the parameter sniffing problem.
You can find details about parameter sniffing in the article “What is Parameter Sniffing“.
It will also help you develop a more understandable application by keeping the transactional logic of the application level in one place (within the sp in the database).
SPs can return results by taking parameters.
For example, you have a table with the Columns of Id and BookName, called Library.
You can create the table in your database with the following script.
|
1 2 3 4 |
CREATE TABLE [dbo].[Library]( [Id] [int] NULL, [BookName] [varchar](500) NULL ) ON [PRIMARY] |
After creating the table, create the SP with the following script.
|
1 2 3 4 5 6 7 8 9 |
CREATE PROCEDURE BookNameSP @Id int --parameter AS BEGIN SET NOCOUNT ON; SELECT BookName from Library WHERE Id = @Id END |
The sp we have created takes the value of Id as a parameter and returns the BookName value that Id has.
We add two lines to our table using the following script.
|
1 2 |
INSERT INTO [dbo].[Library]([Id],[BookName])VALUES(1,'xBook') INSERT INTO [dbo].[Library]([Id],[BookName])VALUES(2,'yBook') |
When you run SP as follows, it will return a record like the one below.
|
1 |
EXEC dbo.BookNameSP 2 |
Result:
yBook
