How to create and execute stored procedure in SQL server ?

 Hi guys, in this blog post we will see how to create stored procedure in SQL and how to execute it. This blog post is useful for SQL server beginners. Let's see it practically.

Stored procedure is set of SQL statements which are created and stored on server, so that we can reuse them.

Syntax for creating stored procedure:-

CREATE PROC <Procedure_Name> 

-- Add the parameters for the stored procedure here

<@Param1> <Datatype_For_Param1> = <Default_Value_For_Param1>

AS

BEGIN

SET NOCOUNT ON;

         -- SQL statements for procedure here

SELECT  COLUMN_NAME1,COLUMN_NAME2  FROM TABLE_NAME

         Where COLUMN_NAME1=@Param1

END

For e.g. We have table with name tblEmployee. We will create stored procedure which will retrieve data from table. 

Please follow below steps to create stored procedure:-

1) Login into SQL server using username and password.

2) Select database in object explorer and click on new query.

3) Use syntax for creating stored procedure as shown below:-

4)After writing query click on execute so, stored procedure will be created in SQL server.

Create proc usp_GetEmployeeDetails

@EmployeeId int

As

Begin

Select EmployeeFirstName+' '+EmployeeLastName as [Employee Name] 

from tblEmployee 

where EmployeeId=@EmployeeId 

End

Syntax for executing stored procedure in SQL server:-

exec Stored_Procedure_Name parameters

We can execute above stored procedure like this:-

Write below query and click on execute option

exec usp_GetEmployeeDetails 1

Refer below screenshot: