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.
<span class="pln"> USE DB02TEST01 </span>
Procedure without output parameter
Create a table Customer
<span class="pln"> CREATE TABLE </span><span class="typ">Customers_1637935</span><span class="pun">(</span><span class="pln"> </span><span class="typ">CustomerID</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="typ">CustomerName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln"> </span><span class="typ">ContactName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln"> </span><span class="typ">Address</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln"> </span><span class="typ">City</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="typ">PostalCode</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Country</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">20</span><span class="pun">)</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span>
Insert Some Data into the table and View the data:
<span class="pln"> INSERT INTO </span><span class="typ">Customers_1637935</span><span class="pln"> </span><span class="pun">(</span><span class="typ">CustomerID</span><span class="pun">,</span><span class="pln"> </span><span class="typ">CustomerName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ContactName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Address</span><span class="pun">,</span><span class="pln"> </span><span class="typ">City</span><span class="pun">,</span><span class="pln"> </span><span class="typ">PostalCode</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Country</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rumman Ansari'</span><span class="pun">,</span><span class="str">'Smile'</span><span class="pun">,</span><span class="str">'Kulut'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Burdwan'</span><span class="pun">,</span><span class="str">'713422'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'London'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Alfreds Futterkiste'</span><span class="pun">,</span><span class="str">'Maria Anders'</span><span class="pun">,</span><span class="str">'Obere Str. 57'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Berlin'</span><span class="pun">,</span><span class="str">'12209'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Germany'</span><span class="pun">)</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Customers_1637935</span><span class="pln"> </span>
Create a Procedure and take a input parameter
<span class="pln"> ALTER PROCEDURE </span><span class="typ">SelectAllCustomers</span><span class="pln"> </span><span class="lit">@City</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">30</span><span class="pun">)</span><span class="pln"> AS </span><span class="kwd">BEGIN</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Customers_1637935</span><span class="pln"> WHERE </span><span class="typ">City</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@City</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> EXEC </span><span class="typ">SelectAllCustomers</span><span class="pln"> </span><span class="str">"Burdwan"</span><span class="pln"> </span>
Use the below code to use a particular database
Procedure without output parameter
Create a table to insert data using the precedure
<span class="pln"> CREATE TABLE </span><span class="typ">EtaxiSystem_1937935</span><span class="pun">(</span><span class="pln"> </span><span class="typ">BookingReferenceNumber</span><span class="pln"> INT PRIMARY KEY IDENTITY</span><span class="pun">(</span><span class="lit">100000</span><span class="pun">,</span><span class="lit">1</span><span class="pun">),</span><span class="pln"> </span><span class="typ">PassengerName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="typ">FromLocation</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="typ">ToLocation</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="typ">DepartureTime</span><span class="pln"> DATETIME</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DistanceinKm</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="typ">EstimatedAmount</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="typ">CabType</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span>
Use the below code to see the table data, is data present or not?
<span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">EtaxiSystem_1937935</span><span class="pln"> </span>
Execute the below code to create a procedure in sql server
<span class="pln"> CREATE PROCEDURE </span><span class="typ">ETaxiProcedure</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@PassengerName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@FromLocation</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@ToLocation</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@DepartureTime</span><span class="pln"> DATETIME</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@DistanceinKm</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@EstimatedAmount</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@CabType</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@BookingReferenceNumber</span><span class="pln"> INT OUT </span><span class="pun">)</span><span class="pln"> AS </span><span class="kwd">BEGIN</span><span class="pln"> INSERT INTO </span><span class="typ">EtaxiSystem_1937935</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@PassengerName</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@FromLocation</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@ToLocation</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@DepartureTime</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@DistanceinKm</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@EstimatedAmount</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@CabType</span><span class="pun">)</span><span class="pln"> SET </span><span class="lit">@BookingReferenceNumber</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">@</span><span class="lit">@IDENTITY</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span>
Execute the code to see your procedure is working or not
<span class="pln"> DECLARE </span><span class="lit">@BookingReferenceNumber1</span><span class="pln"> INT EXEC </span><span class="typ">ETaxiProcedure</span><span class="pln"> </span><span class="str">'Rumman'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kerala'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'12.02.1996'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">10</span><span class="pun">,</span><span class="pln"> </span><span class="str">'indigo'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@BookingReferenceNumber1</span><span class="pln"> OUT PRINT </span><span class="lit">@BookingReferenceNumber1</span><span class="pln"> </span>
Now see the table details using the below code again
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">EtaxiSystem_1937935</span><span class="pln"> </span>
Procedure with different Choice inside Procedure
<span class="pln"> ALTER PROCEDURE </span><span class="typ">SelectAllCustomers1</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@CustomerName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@ContactName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@Address</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@City</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@PostalCode</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@Country</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@CID</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> OUT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@choice</span><span class="pln"> INT</span><span class="pun">)</span><span class="pln"> AS </span><span class="kwd">if</span><span class="pun">(</span><span class="lit">@choice</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> INSERT INTO </span><span class="typ">Customers_1637935</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">@CustomerName</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@ContactName</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@Address</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@City</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@PostalCode</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@Country</span><span class="pun">)</span><span class="pln"> SET </span><span class="lit">@CID</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">@</span><span class="lit">@IDENTITY</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="kwd">else</span><span class="pln"> </span><span class="kwd">if</span><span class="pun">(</span><span class="lit">@choice</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Customers_1637935</span><span class="pln"> SET </span><span class="lit">@CID</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">@</span><span class="lit">@IDENTITY</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span>
Execute the above procedure
<span class="pln"> DECLARE </span><span class="lit">@CID</span><span class="pln"> INT EXEC </span><span class="typ">SelectAllCustomers1</span><span class="pln"> </span><span class="str">'Ansari Rumman'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Smile'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kulut'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Burdwan'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'713422'</span><span class="pun">,</span><span class="str">'London'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@CID</span><span class="pln"> OUT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> PRINT </span><span class="lit">@CID</span><span class="pln"> </span>
