A stored procedure in SQL Server is a precompiled collection of SQL statements and procedural logic that is stored and executed on the server. It provides a way to encapsulate complex logic and perform common tasks. Here's an example of creating a simple stored procedure in SQL Server:

sql
CREATE PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    -- SQL statements and logic here
    SELECT OrderId, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerId = @CustomerId;
END

In this example, we create a stored procedure called `GetCustomerOrders` that accepts a parameter `@CustomerId` of type `INT`. The `BEGIN` and `END` keywords enclose the body of the stored procedure.

Within the procedure, you can include any valid SQL statements, including SELECT, INSERT, UPDATE, DELETE, and control flow statements like IF-ELSE and WHILE loops. In this case, we have a simple SELECT statement that retrieves order information from the `Orders` table based on the provided `@CustomerId`.

To execute the stored procedure, you can use the `EXEC` or `EXECUTE` statement:

sql
EXEC dbo.GetCustomerOrders @CustomerId = 123;

You pass the parameter value `123` to the stored procedure to retrieve orders for the specified customer.

Stored procedures offer several advantages, including code reusability, improved performance, and enhanced security by allowing controlled access to data and abstracting the underlying implementation details. They are commonly used for complex data manipulation, business logic implementation, and improving overall database performance.