Create a procedure with output parameter using sql server

In this blog we will discuss how to create PROCEDURE without output parameter, with output parameter and we will insert the value in the table using the PROCEDURE. Also we will discuss how to use different choice inside the Procedure using if statement.

USE DB02TEST01  

Procedure without output parameter

Create a table Customer

CREATE TABLE Customers_1637935(
CustomerID INT,
CustomerName VARCHAR(30),
ContactName VARCHAR(30),
Address VARCHAR(30),
City VARCHAR(20),
PostalCode INT,
Country VARCHAR(20)
)

Insert Some Data into the table and View the data:

INSERT INTO Customers_1637935 (CustomerID, CustomerName, ContactName, Address, City,
PostalCode, Country) VALUES
(2, 'Rumman Ansari','Smile','Kulut', 'Burdwan','713422', 'London')
(1, 'Alfreds Futterkiste','Maria Anders','Obere Str. 57', 'Berlin','12209', 'Germany')

SELECT * FROM Customers_1637935

Create a Procedure and take a input parameter

ALTER PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
BEGIN
SELECT * FROM Customers_1637935 WHERE City = @City
END
EXEC SelectAllCustomers "Burdwan"

Use the below code to use a particular database

Procedure without output parameter

Create a table to insert data using the precedure

 CREATE TABLE EtaxiSystem_1937935(
 BookingReferenceNumber INT PRIMARY KEY IDENTITY(100000,1),
 PassengerName VARCHAR(50),
 FromLocation VARCHAR(50),
 ToLocation VARCHAR(50),
 DepartureTime DATETIME,
 DistanceinKm INT,
 EstimatedAmount INT,
 CabType VARCHAR(50)
 )  

Use the below code to see the table data, is data present or not?

 select * from EtaxiSystem_1937935

Execute the below code to create a procedure in sql server

CREATE PROCEDURE ETaxiProcedure
 ( 
 @PassengerName VARCHAR(50),
 @FromLocation VARCHAR(50),
 @ToLocation VARCHAR(50),
 @DepartureTime DATETIME,
 @DistanceinKm INT,
 @EstimatedAmount INT,
 @CabType VARCHAR(50),
 @BookingReferenceNumber INT OUT
 )
 AS
 BEGIN

 INSERT INTO EtaxiSystem_1937935 VALUES 
 (
 @PassengerName,
 @FromLocation,
 @ToLocation,
 @DepartureTime,
 @DistanceinKm,
 @EstimatedAmount,
 @CabType)
 SET @BookingReferenceNumber = @@IDENTITY
 END

Execute the code to see your procedure is working or not

DECLARE @BookingReferenceNumber1 INT
EXEC ETaxiProcedure 'Rumman', 'Kolkata', 'Kerala', '12.02.1996', 1, 10, 'indigo', @BookingReferenceNumber1 OUT
PRINT @BookingReferenceNumber1 

Now see the table details using the below code again

SELECT * FROM EtaxiSystem_1937935

Procedure with different Choice inside Procedure

ALTER PROCEDURE SelectAllCustomers1
(
@CustomerName VARCHAR(30),
@ContactName VARCHAR(30),
@Address VARCHAR(30),
@City VARCHAR(20),
@PostalCode INT,
@Country VARCHAR(20),
@CID int OUT, @choice INT)
AS
if(@choice = 1)
BEGIN
INSERT INTO Customers_1637935 VALUES
(@CustomerName, @ContactName, @Address, @City, @PostalCode, @Country)
SET @CID = @@IDENTITY
END
else if(@choice = 2)
BEGIN
SELECT * FROM Customers_1637935
SET @CID = @@IDENTITY
END
 

Execute the above procedure

 
DECLARE @CID INT
EXEC SelectAllCustomers1 'Ansari Rumman', 'Smile', 'Kulut', 'Burdwan', '713422','London', @CID
OUT, 2
PRINT @CID