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.
Create procedure <procedure_Name>
As
Begin
<SQL Statement>
End
Go
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 |
+----+----------+-----+-----------+----------+
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
SQL Server Connection String Formats
posted on 2019-08-27 05:53:53 - SQL Server Tutorials