How to create Stored Procedure in SQL Server with and without parameters?

In this article I am going to explain what is Stored Procedure?  And how to create a simple stored procedure with and without parameter?

Stored Procedure is nothing but single or set queries that are compiled and stored together in database.

We can execute the stored procedure using procedure name.

Advantages of Stored Procedure

• Precompiled execution: SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

• Reduced client/server traffic: If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

• Efficient reuse of code and programming abstraction: Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.

• Enhanced security controls: You can grant users permission to execute a stored procedure independently of underlying table permissions. Stored procedures are very similar to user-defined functions, but there are subtle differences.

Syntax for without parameter:

Create Procedure <procedure_name>

As

Begin

sql query……………..

End

Example:

Create Procedure GetEmployees

As

Begin

Select * From EMPLOYEE_DETAILS

End

To call or execute this stored procedure:

Execute GetEmployees

Syntax for with parameter:

Create Procedure <procedure_name>

@<parametername><type>,

@<parametername><type>

As

Begin

sql query………………….

End

Example:

Create Procedure GetEmployees

@ID Int

As

Begin

Select * From EMPLOYEE_DETAILS Where EMPLOYEE_ID=@ID

End

To call or execute this stored procedure:

Execute GetEmployees 1

Here “1” is represent the parameter as employee id.

Stored Procedure with OUTPUT Parameter

Output parameters allow you to retrieve values from a stored procedure after the stored procedure finishes executing. These values can be set and/or manipulated by the stored procedure. Output parameters can be any data type. This makes them more useful than the return value which only returns an int data type. You can have multiple output parameters.

Syntax for OUTPUT Parameter:

Create Procedure <procedure_name>

@<parametername><type> Output,

@<parametername><type>

As

Begin

sql query………………….

End

Example:

Create Procedure GetEmployees

@ID Int ,

@Name Varchar(50) Output

As

Begin

Select @Name = Emp_Name  From EMPLOYEETABLE Where EMP_ID = @ID

End

To call or execute this stored procedure:

Decalre @Name Varchar(50)

Execute GETDETAILS 1, @Name Output

Select @Name As Employee_Name

I hope you got an overview about what is stored procedure with and without output parameters. thanks for reading this article.