Learn to Create, Execute, and Use Parameters - Textnotes

Learn to Create, Execute, and Use Parameters

Master SQL stored procedures and functions in this beginner-friendly guide. Learn how to create and execute stored procedures, use scalar and table-valued functions, input/output parameters, and fetch employee details or calculate tax on salary with practical examples.

1. Introduction

Stored Procedures and Functions are reusable SQL programs that help simplify complex queries, improve performance, and enforce consistency.

Key Points:

  1. Stored Procedures: Can perform multiple SQL operations, take parameters, and be executed multiple times.
  2. Functions: Return a value (scalar) or table (table-valued) and can be used in queries.

2. Stored Procedures

2.1 Creating Stored Procedures

Syntax:


CREATE PROCEDURE ProcedureName
@Param1 DataType,
@Param2 DataType OUTPUT
AS
BEGIN
-- SQL statements
END;

Example:


-- Procedure to fetch employees by department
CREATE PROCEDURE GetEmployeesByDept
@DeptName VARCHAR(50)
AS
BEGIN
SELECT Name, Salary
FROM Employees
WHERE Department = @DeptName;
END;

2.2 Executing Stored Procedures


EXEC GetEmployeesByDept @DeptName = 'IT';

2.3 Advantages of Stored Procedures

  1. Reusability: Write once, use multiple times.
  2. Performance: Precompiled execution reduces parsing time.
  3. Security: Restrict direct table access; users execute procedures.
  4. Maintainability: Centralized logic for easier updates.

3. Functions

3.1 Scalar Functions

  1. Return a single value.

Syntax:


CREATE FUNCTION FunctionName (@Param DataType)
RETURNS DataType
AS
BEGIN
RETURN -- value
END;

Example:


-- Function to calculate tax on salary
CREATE FUNCTION CalculateTax (@Salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Salary * 0.1; -- 10% tax
END;

Usage:


SELECT Name, dbo.CalculateTax(Salary) AS Tax
FROM Employees;

3.2 Table-Valued Functions

  1. Return a table result.

Example:


CREATE FUNCTION EmployeesInDept (@DeptName VARCHAR(50))
RETURNS TABLE
AS
RETURN
(
SELECT Name, Salary
FROM Employees
WHERE Department = @DeptName
);

Usage:


SELECT * FROM dbo.EmployeesInDept('IT');

4. Practical Exercises

  1. Create a stored procedure to fetch employee details by department.
  2. Execute the procedure for departments like IT, HR, or Sales.
  3. Create a scalar function to calculate tax on salary.
  4. Use the function in a SELECT query to calculate taxes for all employees.
  5. Create a table-valued function to return all employees in a given department.

5. Tips for Beginners

  1. Use stored procedures for complex operations involving multiple queries.
  2. Use functions for calculations or reusable table results.
  3. Always test procedures and functions with different parameters.
  4. Name procedures and functions meaningfully for maintainability.
  5. Prefer table-valued functions when returning multiple rows for queries.


Next Step: After mastering stored procedures and functions, the next module is Triggers in SQL, where you’ll learn to automatically execute SQL code for insert, update, or delete actions.