How to wright a Store Procedure in MS SQL Server

Basic Stored Procedure in SQL Server

CREATE PROCEDURE GetUserById
@UserId INT
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM Users WHERE Id = @UserId;
END;

To run it:

EXEC GetUserById @UserId = 1;

Stored Procedure With Multiple Parameters

CREATE PROCEDURE InsertUser
@Name NVARCHAR(100),
@Email NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Users (Name, Email)
VALUES (@Name, @Email);
END;

To execute it:

EXEC InsertUser @Name = ‘John Doe’, @Email = ‘john@example.com’;

Stored Procedure With Output Parameter

CREATE PROCEDURE GetUserCount
@TotalCount INT OUTPUT
AS
BEGIN
SELECT @TotalCount = COUNT(*) FROM Users;
END;

To use it:

DECLARE @Count INT;
EXEC GetUserCount @TotalCount = @Count OUTPUT;
PRINT @Count;