In-Depth Notes on Functions, Stored Procedures, and Cursors in SQL Server


Functions
  • A function is a reusable chunk of code that performs a specific task. Functions in programming help to break a problem into smaller tasks, enhancing maintainability and clarity in code. A function typically encapsulates a particular functionality that can be invoked multiple times throughout the program, thus promoting code reusability.

  • Functions contain instructions that produce output based on input, and they are organized as blocks of code, often characterized by their ability to return a value after execution, which is crucial in various programming applications.

Characteristics of Functions

  • Database Object: Functions are objects within SQL Server that perform computational tasks, returning results that can be utilized in SQL queries, reports, and applications.

  • Input Parameters: Functions accept only input parameters and cannot modify the database directly; hence, operations such as INSERT, UPDATE, or DELETE are not permitted within a function to ensure data integrity and encapsulation.

  • Return Types: Functions can return either a single value or a complete table, making them versatile for various applications depending on the requirement. The type of value returned is defined in the function declaration, specifying how the information is structured for further data manipulation or retrieval.

Types of Functions

  1. System Functions:

    • Built-in functions available across different databases that provide essential functionality. Types include:

      • Aggregate functions such as COUNT, SUM, AVG, which operate on a set of values and return a single summarizing value.

      • Mathematical functions like ROUND, CEILING, and FLOOR that perform mathematical operations on numeric data.

      • String functions such as SUBSTRING, CHARINDEX, and LEN that are utilized to manipulate text strings.

      • Date and time functions like GETDATE() and DATEDIFF(), which allow for manipulation and extraction of date values.

  2. User Defined Functions (UDFs):

    • Custom functions defined by users to extend the functionality of the database. They can return either:

      • Scalar functions: These return a single value, allowing for simple calculations or data transformations.

      • Table-valued functions: These return a table data type, suitable for complex queries that require multiple records or fields to be returned in a tabular format.

Defining User Defined Functions

  • Syntax to create a UDF:

CREATE OR ALTER FUNCTION function_name (
    @parameter1 datatype,
    @parameter2 datatype,
    ...
) RETURNS return_datatype 
AS 
BEGIN  
   [declaration_section]  
   [executable_section]  
   RETURN return_value  
END;
  • Functions must have a valid name and must return a result after execution. They are limited to working with input parameters, which ensures that they remain deterministic and predictable in their output, a key aspect in processes that require consistent results.

Examples of User Defined Functions

  1. Scalar Valued Function

CREATE FUNCTION fun_AddNumber(@num1 int, @num2 int) RETURNS int AS  
BEGIN  
    RETURN @num1 + @num2  
END;  
-- To execute:  
SELECT dbo.fun_AddNumber(2,3);  -- Output: 5
  1. String Concatenation

CREATE FUNCTION fun_JoinPersonInfo(@FirstName nvarchar(150), @LastName nvarchar(500)) RETURNS varchar(500) AS  
BEGIN  
    RETURN (@FirstName + ' ' + @LastName)  
END;  
-- To execute:  
SELECT dbo.fun_JoinPersonInfo(FirstName, LastName) AS Merged FROM Person;
  1. Odd/Even Check

CREATE FUNCTION FN_CHECKEVENODD(@NO INT) RETURNS VARCHAR(50) AS  
BEGIN  
    DECLARE @MSG VARCHAR(50)  
    IF (@NO % 2 = 0)  
        SET @MSG = 'EVEN'  
    ELSE  
        SET @MSG = 'ODD'  
    RETURN @MSG  
END;  
-- To execute:  
SELECT dbo.FN_CHECKEVENODD(12);
  1. Function with Loop

CREATE FUNCTION FN_evensum_1TO50() RETURNS VARCHAR(200) AS  
BEGIN  
    DECLARE @sum int, @i INT  
    SET @sum = 0  
    SET @i = 1  
    WHILE(@i <= 50)  
    BEGIN  
        IF(@i % 2 = 0)  
            SET @sum = @sum + @i  
        SET @i = @i + 1  
    END  
    RETURN @sum  
END;  
-- To execute:  
SELECT dbo.FN_evensum_1TO50();  -- Output: 650
  1. Table Valued Function

CREATE FUNCTION fn_PersonInformation() RETURNS TABLE AS  
RETURN (SELECT * FROM Person WHERE Salary > 15000);  
-- To execute:  
SELECT * FROM fn_PersonInformation();
  1. Handling Multiple Records

CREATE FUNCTION fn_SongwithAlbum() RETURNS @Temp TABLE (SongTitle varchar(100), AlbumTitle VARCHAR(100)) AS  
BEGIN  
    INSERT INTO @Temp  
    SELECT Song_title, Album_title FROM Songs JOIN Albums ON Songs.Album_id = Albums.Album_id;  
    RETURN  
END;  
-- To execute:  
SELECT * FROM fn_SongwithAlbum();

Important Considerations for UDFs

  • A function must return a value; they do not support exception handling or DML operations (INSERT, UPDATE, DELETE), which limits their functionality but promotes their reliability and consistency across database operations. It is essential to understand these constraints when designing functions to ensure they meet the necessary requirements for your application.

  • User Defined Functions can't return XML data type, which may limit their usability depending on the data processing needs. Ensuring appropriate use of data types and understanding their limitations is crucial for effective database design.

Stored Procedures
  • A stored procedure is a compiled set of SQL statements stored in the database for reuse, enabling the execution of complex operations with a single command without the need to resend the SQL code each time it is called. This can significantly enhance performance and reduce errors in repeat operations.

  • Structure: Stored procedures consist of a header (containing the name and parameters) and a body (which consists of declaration, execution, and handling of exceptions). This structure provides clarity and organization to the operations performed within the procedure.

  • Stored procedures can return values, sometimes multiple values depending on the logic implemented, making them versatile tools for data manipulation and reporting.

Creating Stored Procedures

  • Syntax:

CREATE OR ALTER PROCEDURE Procedure_Name  
AS  
BEGIN  
   -- SQL statements  
END;

Types of Parameters

  1. IN Parameters: Allow input of values into procedures, enabling dynamic execution based on user input.

  2. OUT Parameters: Facilitate retrieval of output values from procedures, allowing data to be returned to the calling context.

  3. INOUT Parameters: Pass values into a procedure and return output values, which is particularly useful in scenarios where input values need to be modified during the course of the procedure execution.

Example of Stored Procedures

  1. Procedure Without Parameters

CREATE PROCEDURE PR_Customer_SelectByName AS  
    SELECT Name FROM Customer;  
-- Execution:  
EXEC PR_Customer_SelectByName;
  1. Procedure With Parameters

CREATE PROCEDURE PR_Customer_SelectByPK @CstID int IN AS  
    SELECT Name, Age, City FROM Customer WHERE CstID = @CstID;  
-- Execution:  
EXEC PR_Customer_SelectByPK 103;
  1. Procedure for Inserting Data

CREATE PROCEDURE PR_Customer_Insert @CstID int, @Name varchar(20)...  
AS  
INSERT INTO Customer VALUES (@CstID, @Name, ...);  
-- Execution:  
EXEC PR_Customer_Insert 106, 'Umesh', ...;
  1. Procedure for Updating Data

CREATE PROCEDURE PR_Customer_Update @CstID int...  
AS  
UPDATE Customer SET Name = @Name WHERE CstID = @CstID;  
-- Execution:  
EXEC PR_Customer_Update 106, 'Raj', ...;
  1. Procedure for Deleting Data

CREATE PROCEDURE PR_Customer_Delete @CstID int AS  
DELETE FROM Customer WHERE CstID = @CstID;  
-- Execution:  
EXEC PR_Customer_Delete 106;
Cursor
  • A cursor is a temporary workspace allocated by the database server specifically designed for handling DML operations in SQL environments. Cursors allow for the retrieval and manipulation of data when row-by-row operations are necessary, providing a powerful approach to complex querying.

  • Types of Cursors:

    • Implicit Cursor: Automatically managed by SQL Server during DML operations, making them user-friendly and efficient for basic operations.

    • Explicit Cursor: Defined and managed by users; these cursors provide greater control over data processing and are useful for more complex query requirements.

SQL Cursor Life Cycle

  1. Declare: A cursor is declared for a SELECT statement, specifying the data to be processed.

  2. Open: The cursor is opened to fetch records from it.

  3. Fetch: Rows are processed individually, allowing for detailed operations on each row.

  4. Close: The cursor is closed after processing to release resources.

  5. Deallocate: The cursor is deallocated to free up memory and other resources, ensuring optimal use of server capabilities.

Example of Using a Cursor

DECLARE @FirstName VARCHAR(250), @Salary DECIMAL(8,2);  
DECLARE cursor_person CURSOR FOR SELECT FirstName, Salary FROM Person;  
OPEN cursor_person;  
FETCH NEXT FROM cursor_person INTO @FirstName, @Salary;  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @FirstName + '-' + CAST(@Salary AS varchar);  
    FETCH NEXT FROM cursor_person INTO @FirstName, @Salary;  
END;  
CLOSE cursor_person;  
DEALLOCATE cursor_person;

Applications of Explicit Cursors

  • Enable row-by-row processing for complex logic, which is essential for operations that require detailed examination or transformation of data.

  • Provide control over fetching mechanisms to optimize performance and resource management, particularly in large datasets.

  • Improve readability and organization for complex SQL operations through clear structure and defined boundaries of processing logic, enhancing both development and maintenance.

Important Error Messages Related to Stored Procedures
  • Error 2714: Object already exists; indicates an attempt to create a stored procedure or function with a name that is already in use.

  • Error 201: Expected parameter not supplied; typically occurs when executing a procedure that requires parameters not provided in the execution call.

  • Error 8114: Data type conversion error; can arise when attempting to pass values of incompatible types to a procedure.

Exercises Related to Stored Procedures
  1. Create procedures returning branch and semester-wise maximum SPI details to analyze student performance more effectively.

  2. Show details for students in the CE branch only, enabling filtered views of specific courses.

  3. Create a procedure to insert a record into the student table, facilitating efficient data entry.

  4. Create a procedure to show details for the first 5 students, which may assist in preliminary data reports.

  5. Accept Semester & Branch as input and return records accordingly, allowing dynamic query generation for academic purposes.