SQL Server -Create Stored Procedure with Parameter

-

Create Parameterizes STORED PROCEDURE IN SQL :


In this post we learn how to work with Sql-store procedure with parameterize. In my old post we describe what the Store procedure, how many types of procedure in sql and how to create simple store procedure.

Meaning of Parameter:

A Parameter is refers to:

“Measurable factor forming one of a set that defines a system or sets the conditions of its operation”.

The meaning of Parameter is a characteristic, feature, or measurable factor that can help in defining a particular system.


How to create parameterizes stored procedure in sql:


 In this last post “starting work with store procedure” we explain how to start creating and managing sql store procedure clearly. Now we give the example of how to pass parameter in procedure.


Sql- Create a Stored Procedure by sql code:


 Alter Procedure Poc_Login
(
-- Declear variable here .....
@QueryType varchar(50)=null,
@Name varchar(50)='Jon',
@Password varchar(50)=null
)

As
begin
--write your bussionss Login here.
select Name,Password from Login_table where Name=@Name

end


SQL-Create Stored Procedure in SQL Server:

We can create as we want in store procedure similarly. When I am working in my own project the we use separate procedure for each table. By this you can make all possible queries and run these queries when you want by procedure. Here we listed some importance/benefits of sql-procedures.

Benefits of Stored Procedures:

  • Stored Procedures reduce the overhead of compiling each time.
  • Create once and call it N number of times
  • Reduce traffic.
  • You can execute selected Query with store procedure.

How to Run This Procedure in sql server:


 USE [my_testing]
GO
DECLARE     @return_value int

EXEC  @return_value = [dbo].[Poc_Login]
SELECT      'Return Value' = @return_value
GO

Result of this Sql Procedure:





Sql Server Interview Qus:



Comments

Post a Comment