Stored Procedure in Asp.net Core

In this article I am going to discuss about the stored procedure in asp.net core, how to create stored procedure in sql server and how to call stored procedure in asp.net core web api.

Introduction to Stored Procedure

A stored procedure is a precompiled collection of one or more SQL statements that are stored in a database and can be invoked by name. Stored procedures offer several advantages, including improved performance, code reusability, and security. Here’s the basic structure of a stored procedure in SQL:

How to Create Stored Procedure in Sql Server

For creating stored procedure in sql server you have to install Sql Server in your system with SSMS (Sql Server Management Studio). Once your setup is complete you will be able to use stored procedure.

 -- Create stored procedure
CREATE procedure spInsertUser  @Name varchar(10),@Email varchar(10), @Password     varchar(10)
  as
  begin
  insert into Users(Id,Name,Email,Password)Values(newId(),@Name,@Email,@Password)
  select * from Users
  end

-- Edit stored procedure
 ALTER procedure spInsertUser  @Name varchar(10),@Email varchar(10), @Password varchar(10)
  as
  begin
  insert into Users(Id,Name,Email,Password)Values(newId(),@Name,@Email,@Password)
  select * from Users
  end

How to Call Stored Procedure in sql server

See the stored procedure in the table

EXEC sp_helptext 'spInsertUser';
 

Execute the stored procedure

-- if there is no parameter in sql procedure then we can call 
exec spInsertUser

-- if there is parameter in sql procedure then we can like
exec spInsertUser 'WebTutorialStack','WebTutorialStack@gmail.com','WebTutorialStack'

you can see the stored procedure by following these simple steps

• Go to your database
• Under database< Programmability
• Choose database<Programmability<Stored Procedures
• Now you can see the stored procedure.

How to Call Stored Procedure in asp.net core web API

In asp.net core sometimes it is very good to call stored procedure for improving the performance of the web application. By calling stored procedure in your website the speed performance of your application will increase and this will also help in optimization of site. For calling Stored Procedure in asp.net core web api you have to call simple command in your service.

string storedProcedure = "exec spInsertUser "+"@Name= '" + model.Name +"'," + "@Email= '" + model.Email +"',"+ "@Password='" + model.Password+"'";
                await _context.Users.FromSqlRaw(storedProcedure).ToListAsync();
                return true;

In above code _context.Users is referring to my Users table in my database and on that database there is stored Procedure named spInsertUser which takes three parameters Name, Email and Password therefore I have passed three parameter’s value while calling the stored procedure.

Note:-

1. While naming the user-defined stored procedures we should not have to use “sp_” as a prefix as it is recommended by Microsoft. The reason is all the system-defined procedures in SQL Server are prefixed with “sp_”. So to avoid the ambiguity between the user-defined stored procedure and the system stored procedures and for any conflicts with some future coming system procedure we should not use sp_ as a prefix to our user-defined stored procedure.