SQL Server Procedures

Stored procedures are a collection of T-SQL statements that are saved in the SQL Server database. Rather than issuing many statements, you may issue a single code to invoke the stored procedure to do a handful of work.
Furthermore, because the code is kept in the database, you may issue the same piece of code several times, even from various programmes or a query window. If you frequently write the same SQL statement, save it as a stored procedure. That way, all you have to do to put it into action is call it.
A user-defined stored procedures includes SQL statements for accessing, modifying, or deleting records in the SQL Server database. A stored procedure can accept input variables and return output values.

User Permissions

CREATE PROCEDURE permission in the database.
ALTER permission on the schema.

How to create SQL Server Procedures?

To begin, launch your local SQL Server Management Studio.
Connect to a Database Engine instance in Object Explorer and then extend that instance.
Expand Databases first, then the AdventureWorks database, and finally Programmability.
Right-click Stored Procedures and then select New Stored Procedure
Within the dialogue box, you may now specify values for template parameters.
When finished, click OK.
Modify the SELECT statement in the Query Editor with desired inputs.
To test the syntax, select Parse (Ctrl+F5) from the Query menu.

Syntax to create Procedures with T-SQL

USE DatabaseName;
GO
CREATE [ OR ALTER ] { PROC | PROCEDURE }
@parameter_name data_type,
@parameter_name2 data_type, ...
AS
BEGIN
SET NOCOUNT ON
SQL_statement
END
GO

Create Procedure Example

Customers Table

Contracts Table

Basic Procedure

CREATE PROCEDURE TotalContractsByCustomer
AS
BEGIN
SET NOCOUNT ON
SELECT c.customer_name, count(ctr.customer_id) AS TotalContracts
FROM customers c, contracts ctr
WHERE c.customer_id = ctr.customer_id
GROUP BY c.customer_name
END
GO

EXEC TotalContractsByCustomer
GO

Procedure with parameters

CREATE PROCEDURE SalesByCustomer
  @CustomerName nvarchar(50)
AS
SELECT c.customer_name, sum(ctr.amount) AS TotalAmount
  FROM customers c, contracts ctr
WHERE c.customer_id = ctr.customer_id
     AND c.customer_name = @CustomerName
GROUP BY c.customer_name
GO

EXEC SalesByCustomer 'CUSTOMER_1'
GO