Functions vs Stored Procedures in SQL Server
Functions
- Examples
- Calculate the total price of an order based on item prices and quantities.
- Retrieve a list of employees who have been with the company for more than 5 years.
- Usage
Can be used directly in SQL statements, like a column expression or in WHERE clauses. - Syntax
CREATE FUNCTION function_name ([parameter_list]) RETURNS data_type BEGIN -- Function body RETURN value; END
- Purpose
Return a single value or a table.
Stored Procedures
- Examples
- Insert a new customer record into a table.
- Generate a report summarizing sales data for a specific period.
- Usage
Called by name using the EXECUTE or EXEC keyword. - Syntax
CREATE PROCEDURE procedure_name ([parameter_list]) AS BEGIN -- Procedure body END
- Purpose
Perform a series of SQL statements and return multiple results or update data.
Key Differences
Feature | Functions | Stored Procedures |
---|---|---|
Purpose | Return a single value or table | Perform multiple SQL statements |
Syntax | RETURNS data_type | No RETURN statement |
Usage | Directly in SQL statements | Called by name |
Results | Single value or table | Multiple results or updates |
When to Use Which
- Stored Procedures
- Perform complex operations or update data.
- Encapsulate logic for reuse and modularity.
- Improve performance by reducing network traffic.
- Functions
- Calculate values or retrieve data for a single result.
- Use them directly in SQL statements for better readability and maintainability.
Function Example: Calculating Total Price
CREATE FUNCTION CalculateTotalPrice(@Quantity INT, @UnitPrice DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
BEGIN
RETURN @Quantity * @UnitPrice;
END
Usage
SELECT ProductName, CalculateTotalPrice(Quantity, UnitPrice) AS TotalPrice
FROM Products;
Stored Procedure Example: Inserting a New Customer
CREATE PROCEDURE InsertCustomer
@CustomerID INT,
@CustomerName VARCHAR(50),
@Address VARCHAR(100)
AS
BEGIN
INSERT INTO Customers (CustomerID, CustomerName, Address)
VALUES (@CustomerID, @CustomerName, @Address);
END
EXEC InsertCustomer 1001, 'John Doe', '123 Main St';
Key Differences in the Examples
- Stored Procedure
Doesn't return a value but performs an action (inserting a new customer). It's called using theEXEC
keyword. - Function
Returns a single value (the total price). It can be used directly in SQL statements likeSELECT
.
Additional Examples
Function: Retrieving the Current Date
CREATE FUNCTION GetCurrentDate()
RETURNS DATE
BEGIN
RETURN GETDATE();
END
Stored Procedure: Updating Product Prices
CREATE PROCEDURE UpdateProductPrices
@PriceIncrease DECIMAL(10, 2)
AS
BEGIN
UPDATE Products
SET UnitPrice = UnitPrice + @PriceIncrease;
END
Alternative Methods to Functions and Stored Procedures in SQL Server
While functions and stored procedures are powerful tools in SQL Server, there are alternative approaches that might be suitable for certain scenarios:
Common Table Expressions (CTEs)
- Advantages
- Improve query readability and performance by avoiding subqueries.
- Can be used to create recursive queries.
- Syntax
WITH CTE_Name AS ( SELECT ... FROM ... WHERE ... ) SELECT ... FROM CTE_Name
- Purpose
Create temporary result sets that can be referenced multiple times within a single SELECT statement.
Inline Table-Valued Functions (TVFs)
- Advantages
- Can be used directly in SELECT statements like a table.
- Provide a more flexible way to return multiple rows.
- Syntax
CREATE FUNCTION FunctionName (@parameter_list) RETURNS TABLE AS RETURN ( SELECT ... FROM ... WHERE ... )
- Purpose
Return a table-valued result from a function.
User-Defined Aggregates (UDAs)
- Advantages
- Extend SQL Server's built-in aggregate functions.
- Syntax
CREATE AGGREGATE AggregateName ([parameter_list]) RETURNS data_type AS BEGIN -- Aggregate function implementation END
- Purpose
Define custom aggregate functions that operate on a set of values.
Window Functions
- Advantages
- Calculate running totals, moving averages, and other window-based calculations.
- Provide a powerful way to analyze data.
- Syntax
SELECT ... OVER ( PARTITION BY ... ORDER BY ... ) FROM ...
- Purpose
Perform calculations over a set of rows related to the current row.
Choosing the Right Method
The best approach depends on the specific requirements of your application. Here are some factors to consider:
- Flexibility
Inline TVFs and UDAs provide more flexibility in terms of the data they return or the calculations they perform. - Readability
Choose methods that improve the readability and maintainability of your code. - Performance
Consider the performance implications of each method, especially for large datasets or complex queries. - Complexity
For simple calculations or data retrieval, functions or CTEs might be sufficient. For more complex operations, stored procedures or UDAs might be better suited.
sql sql-server t-sql