Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Input Parameters

Input parameters allow a Stored Procedure to receive values from the user or application at runtime.

What are Input Parameters?

Input parameters are values passed to a Stored Procedure when it is executed.

They make Stored Procedures reusable because the same procedure can work with different input values.

For example, instead of creating separate procedures for different Student IDs, we can create one procedure that accepts StudentId as an input parameter.

Basic Syntax

CREATE PROCEDURE ProcedureName
    @ParameterName DataType
AS
BEGIN
    SELECT *
    FROM TableName
    WHERE ColumnName = @ParameterName;
END;

Here, @ParameterName represents the input parameter passed to the Stored Procedure.

Example: Student ID Parameter

Suppose we have a Students table with columns such as StudentId, Name, City and Age.

CREATE PROCEDURE GetStudentById
    @StudentId INT
AS
BEGIN
    SELECT *
    FROM Students
    WHERE StudentId = @StudentId;
END;

Executing a Procedure with Input Parameter

After creating the Stored Procedure, we can execute it by passing a value to the input parameter.

EXEC GetStudentById @StudentId = 101;
Explanation:
Here, 101 is passed as the value of the StudentId parameter.

Input Parameter with VARCHAR

Input parameters can also accept text values.

CREATE PROCEDURE GetStudentsByCity
    @City VARCHAR(50)
AS
BEGIN
    SELECT *
    FROM Students
    WHERE City = @City;
END;

Executing VARCHAR Parameter

EXEC GetStudentsByCity @City = 'Pune';

This procedure returns students whose city is Pune.

Multiple Input Parameters

A Stored Procedure can accept more than one input parameter.

CREATE PROCEDURE GetStudentsByAgeAndCity
    @City VARCHAR(50),
    @Age INT
AS
BEGIN
    SELECT *
    FROM Students
    WHERE City = @City
      AND Age = @Age;
END;

Executing Multiple Parameters

EXEC GetStudentsByAgeAndCity
    @City = 'Pune',
    @Age = 22;
This query returns students whose City is Pune and Age is 22.

Named Parameter Execution

Named parameters make the EXEC statement easier to understand.

EXEC GetStudentsByAgeAndCity
    @Age = 22,
    @City = 'Pune';

The parameters can be supplied by name, so their order does not need to match the procedure definition.

Default Parameter Value

We can also provide a default value for an input parameter.

CREATE PROCEDURE GetStudentsByCity
    @City VARCHAR(50) = 'Pune'
AS
BEGIN
    SELECT *
    FROM Students
    WHERE City = @City;
END;

Executing Default Parameter

If we do not provide a value, SQL Server uses the default value.

EXEC GetStudentsByCity;

In this example, the procedure uses Pune automatically.

Practical Example

The following Stored Procedure accepts StudentId and returns the details of that particular student.

CREATE PROCEDURE GetStudentDetails
    @StudentId INT
AS
BEGIN
    SELECT
        StudentId,
        Name,
        City,
        Age
    FROM Students
    WHERE StudentId = @StudentId;
END;
Execute:
EXEC GetStudentDetails @StudentId = 101;

Important Points

  • Input parameters receive values from the caller.
  • Parameters can have different data types such as INT, VARCHAR, DATE and DECIMAL.
  • A Stored Procedure can have multiple input parameters.
  • Parameters can have default values.
  • Input parameters make Stored Procedures reusable.
  • Use named parameters when executing procedures for better readability.

Summary

Input Parameters allow Stored Procedures to receive values during execution. They make procedures flexible and reusable. A procedure can accept one or multiple parameters, and parameters can also have default values. Input parameters are commonly used to filter data and perform operations based on values supplied by the user or application.