A SQL stored procedure is a set of SQL code that can be saved and reused.
CREATE PROCEDURE getEmployeesBySalary
@minSalary int
AS
BEGIN
SELECT firstName, lastName
FROM Employees
WHERE salary > @minSalary
END
GO
To call this procedure
EXEC getEmployeesBySalary 50000
FUNCTIONS
A SQL function is a set of SQL statements that perform a specific task. Functions must return a value or result. We can use these functions in SELECT, INSERT, DELETE, UPDATE statements.
There are two types of functions in SQL:
Scalar functions, which return a single value and can be used where single expressions are used. For instance:
CREATE FUNCTION addNumbers(@a int, @b int)
RETURNS int
AS
BEGIN
RETURN @a + @b
END
Table-valued functions, which return a table. They can be used in JOIN clauses as if they were a normal table. For example:
CREATE FUNCTION getBooks (@authorID INT)
RETURNS TABLE
AS
RETURN (
SELECT books.title, books.publicationYear
FROM books
WHERE books.authorID = @authorID
)
To call this function
SELECT title, publicationYear
FROM getBooks(3)