Use of STORED PROCEDURES in SQL:
(1) “STORED PROCEDURES” is a type of function in SQL Query which is used in other queries again and again. You can also pass parameters so that it gives results according to the given parameter.
(2) Syntax of “STORED PROCEDURE” statement is:
| CREATE PROCEDURE PROCEDURE_NAME AS SQL STATEMENT GO; |
(3) Let’s take an example, Created Procedure to get the count of vendors according to the “City” column in Vendor Table.
(4) Select the database in the SQL server and click on the “New Query” button as shown.
(5) After that new query editor open as shown.
(6) After doing the above steps, write a query in the editor, as shown.
Source Code:
| –// Creation of STORED PROCEDURE Create Procedure __CountOfVendorInCity Create Procedure AS Select Count(No_) AS [Vendor Count],City from dbo.[CRONUS India Ltd_$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972] Group BY City Order By Count(No_) Desc Go –// Creation of STORED PROCEDURE |
(7) After writing the query, then execute the query (Shortcut keys is F5) as shown.
(8) After creation, the stored procedure shown in “Programmability”>Stored Procedure, as shown.
(9) Use this procedure in multiple queries.
