CREATE PROCEDURE



CREATE PROCEDURE in SQL Server


CREATE procedure is a STORED procedure used to save time to write code again and again by storing the same in database and also to get the required output by passing parameters.

Syntax
Create procedure <procedure_Name> 
As 
Begin 
<SQL Statement> 
End 
Go
Example

Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+                        
| ID | NAME     | AGE | ADDRESS   | SALARY   |                              
+----+----------+-----+-----------+----------+                                
|  1 | Ranith  |  32 | Ahmedabad |  2000.00 |                           
|  2 | Krupa   |  25 | Delhi     |  1500.00 |                                                              
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |                                
|  5 | Munny   |  24 | Indore    | 10000.00 |                                
+----+----------+-----+-----------+----------+                                     

Following command is an example which would fetch all records from the CUSTOMERS table in Testdb database.

CREATE PROCEDURE SelectCustomerstabledata 
AS 
SELECT * FROM Testdb.Customers 
GO

The above command will produce the following result.

+----+----------+-----+-----------+----------+                        
| ID | NAME     | AGE | ADDRESS   | SALARY   |                              
+----+----------+-----+-----------+----------+                                
|  1 | Ranith  |  32 | Ahmedabad |  2000.00 |                           
|  2 | Krupa   |  25 | Delhi     |  1500.00 |                                                              
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |                                
|  5 | Munny   |  24 | Indore    | 10000.00 |                                
+----+----------+-----+-----------+----------+                                     

STORED PROCEDURE with one parameter
Syntax
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;
STORED PROCEDURE with multi parameters
Syntax
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Connection String

SQL Server Connection String Formats

posted on 2019-08-27 05:53:53 - SQL Server Tutorials


UNION

union in SQL server

posted on 2019-08-09 23:05:33 - SQL Server Tutorials


System Functions

System Functions in SQL Server

posted on 2019-08-09 06:14:33 - SQL Server Tutorials


Prompt Examples

ChatGPT Prompt Examples

posted on 2023-06-21 22:37:19 - ChatGPT Tutorials


Use Cases

Chat GPT Key Use Cases

posted on 2023-06-21 21:03:17 - ChatGPT Tutorials


Prompt Frameworks

Prompt Frameworks

posted on 2023-06-21 19:33:06 - ChatGPT Tutorials