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
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.
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
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
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;
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);
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
Table Valued Function
CREATE FUNCTION fn_PersonInformation() RETURNS TABLE AS
RETURN (SELECT * FROM Person WHERE Salary > 15000);
-- To execute:
SELECT * FROM fn_PersonInformation();
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
IN Parameters: Allow input of values into procedures, enabling dynamic execution based on user input.
OUT Parameters: Facilitate retrieval of output values from procedures, allowing data to be returned to the calling context.
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
Procedure Without Parameters
CREATE PROCEDURE PR_Customer_SelectByName AS
SELECT Name FROM Customer;
-- Execution:
EXEC PR_Customer_SelectByName;
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;
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', ...;
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', ...;
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
Declare: A cursor is declared for a SELECT statement, specifying the data to be processed.
Open: The cursor is opened to fetch records from it.
Fetch: Rows are processed individually, allowing for detailed operations on each row.
Close: The cursor is closed after processing to release resources.
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
Create procedures returning branch and semester-wise maximum SPI details to analyze student performance more effectively.
Show details for students in the CE branch only, enabling filtered views of specific courses.
Create a procedure to insert a record into the student table, facilitating efficient data entry.
Create a procedure to show details for the first 5 students, which may assist in preliminary data reports.
Accept Semester & Branch as input and return records accordingly, allowing dynamic query generation for academic purposes.