Welcome to week 8 of 710, Advanced Database Systems. Three database objects for storing procedural code are stored procedures, user-defined functions, and triggers. This means that they are stored within the database and can be recalled at any time to run in real-time. To create these objects, you use the DDL CREATE statements and these objects remain as a part of the database until they're explicitly dropped.
Stored procedures, user-defined functions, and triggers differ by how they're executed. Stored procedures and user-defined functions can be run from any database connection that can run a SQL statement. By contrast, triggers run automatically on the server in response to an event that occurs on a table within th database.
Stored procedures are frequently written by database developers for use by other application programmers. This can simplify the way other programmers interact with a database and will allow a database specialist to create tools that are complete, secure, handled, and documents while allowing application developers to focus on programming the application and interacting with the database in a way very similar to executing an object-oriented object. In addition, you can provide access to a database exclusively through stored procedures maintaining user and role management resulting in control over the security of the data.
Both user-defined functions and triggers are used more often by SQL programmers than by application programmers. SQL programmers often use their own functions within the scripts, stored procedures, and triggers they write, enabling the architecture of the procedural scripting to be somewhat object oriented. Since triggers run in response to an action query, SQL programmers use them to help prevent errors caused by inconsistent or invalid data.
Stored procedures, functions, and triggers also differ by whether or not they can use parameters. Parameters are values that can be passed to or returned from a procedure. Both stored procedures and user-defined functions can use parameters, but triggers can't.
A stored procedure is a way to store coded database scripts that can be recalled at any time to be run in real-time. They do not contain anything other then the script itself, they do not contain other objects or data.
Stored Procedures are pre-compiled when created or altered to optimize performance when executed. There is a RECOMPILE option to prevent this, but it is seldom used as it can greatly reduce system performance.
Here is a simple stored procedure that returns the current date and time:
CREATE PROCEDURE GetDateAndTime
AS
BEGIN
SELECT GETDATE() AS CurrentDateTime
END
Here is a simple procedure that returns the results if a SELECT statement!
CREATE PROCEDURE GetCustomers
AS
BEGIN
SELECT
customerID,
firstName,
lastName,
email,
phone
FROM Customers
ORDER BY lastName, firstName;
END
The following is the general syntax used when creating a stored procedure!
CREATE OR ALTER PROCEDURE procedure_name
[ { @parameter_name data_type } [ ,...n ] ]
AS
BEGIN
SQL statements
END
Notes:
CREATE OR ALTER will allow the developer to make changes to the procedure without the requirement to DROP and re-CREATE it.@parameter_name is the name of the parameter that will be passed to the procedure. The data_type is the type of data that will be passed to the procedure. The data_type can be any valid SQL data type.
SQL statements are the statements that will be executed when the procedure is called.
WARNING: because of the object oriented nature of scripted objects in databases, it is critical that you determine and dependencies between objects before dropping and objects that could potentially break other objects. Documentation of the architecture and of each object specifically is really important.
To DROP a procedure in SQL Server, the following script can be used:
DROP PROC IF EXISTS <procedure name>;Stored procedures can be executed using the EXEC command. The following is an example of how to execute the GetDateAndTime procedure:
EXEC GetDateAndTime;Stored procedures can also be executed using the EXECUTE command. The following is an example of how to execute the GetDateAndTime procedure:
EXECUTE GetDateAndTime;In both stored procedures and function, parameters can be declared, very similar to function in application coding. Stored procedures have an added feature that can allow parameters to not only be input parameters, but also output parameters. These will operate very much like passing parameters by reference, rather than by value.
Parameters can often be used just like variables and can be changed and manipulated within the object. However, INPUT parameters will act like local variables and not impact the sender variable, but OUTPUT parameters will actually have their values returned through the calling statement resulting in potential change of values in the calling algorithm
Here is an example of a stored procedure that uses an input parameter:
CREATE PROCEDURE GetCustomerByID
@customerID INT
AS
BEGIN
SELECT
customerID,
firstName,
lastName,
email,
phone
FROM Customers
WHERE customerID = @customerID;
END
By default parameters are INPUT only parameters unless otherwise indicated.
An example of executing the above procedure is:
EXEC GetCustomerByID 1;Here is an example of a stored procedure that uses an output parameter:
CREATE PROCEDURE GetCustomerByID
@customerID INT,
@firstName NVARCHAR(50) OUTPUT,
@lastName NVARCHAR(50) OUTPUT,
@email NVARCHAR(50) OUTPUT,
@phone NVARCHAR(50) OUTPUT
AS
BEGIN
SELECT
@firstName = firstName,
@lastName = lastName,
@email = email,
@phone = phone
FROM Customers
WHERE customerID = @customerID;
END
An example of executing the above procedure is:
DECLARE @firstName NVARCHAR(50);
DECLARE @lastName NVARCHAR(50);
DECLARE @email NVARCHAR(50);
DECLARE @phone NVARCHAR(50);
EXEC GetCustomerByID 1, @firstName OUTPUT, @lastName OUTPUT, @email OUTPUT, @phone OUTPUT;
SELECT @firstName, @lastName, @email, @phone;Stored procedures can also return values using the RETURN statement. The following is an example of a stored procedure that returns a value:
CREATE PROC spInvCount
@DateVar date = NULL,
@VendorVar varchar(40) = '%'
AS
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
DECLARE @InvCount int;
SELECT @InvCount = COUNT(InvoiceID)
FROM Invoices i
JOIN Vendors v ON i.VendorID = v.VendorID
WHERE (InvoiceDate >= @DateVar) AND (VendorName LIKE @VendorVar);
RETURN @InvCount;An example of executing the above procedure is:
DECLARE @InvCount int;
EXEC @InvCount = spInvCount '2023-01-01', 'P%';
PRINT 'Invoice count: ' + CONVERT(varchar, @InvCount);In addition to using a TRY...CATCH statement to handle errors after they occur, you can also prevent errors before they occur by checking data before it's used to make sure it's valid. Checking data before it's used is known as data validation, and it often makes sense to perform data validation within a stored procedure. Then, if the data is not valid, you can execute code that makes it valid, or you can return an error to the calling code. To return an error, it's often helpful to use the THROW statement. Then, if the calling program contains a TRY...CATCH statement, it can catch and handle the error. Otherwise, the client connection is terminated immediately.
SQL Server 2012 introduced the THROW statement that can raise a specific error number, which must be 50000 or greater, and an error message. You can use this value to indicate the type of error that occurred along with the associated documentation. The state code is strictly informational and has no system meaning. You can use any value between 0 and 255 to represent the state that the system was in when the error was raised. In most cases, you can code 1 for this argument. The stored procedure below illustrates how the THROW statement works.
The syntax of the THROW statement
THROW [error_number, message, state]A stored procedure that tests for a valid foreign key
CREATE OR ALTER PROCEDURE spInsertInvoice
@VendorID
@InvoiceDate date,
@InvoiceTotal money,
@TermsID
AS
IF EXISTS(SELECT * FROM Vendors WHERE VendorID = @VendorID)
INSERT Invoices
VALUES (@VendorID, @InvoiceNumber, @InvoiceDate, @InvoiceTotal, 0, 0, @TermsID, @InvoiceDueDate, NULL);
ELSE
THROW 50001, 'Not a valid VendorID!', 1;A script that calls the procedure
BEGIN TRY
EXEC spInsertInvoice 799,'ZXK-799','2023-03-01',299.95,1,'2023-04-01';
END TRY
BEGIN CATCH
PRINT 'An error occurred.';
PRINT 'Message: ' + CONVERT(varchar, ERROR_MESSAGE());
IF ERROR_NUMBER() >= 50000
PRINT 'This is a custom error message.';
END CATCH;The response from the system
An error occurred.
Message: Not a valid VendorID!
This is a custom error message.This procedure checks the VendorID that's passed from the calling program before it performs the insert operation that's specified by the INSERT statement. That way, the system error that's raised when you try to insert a row with an invalid foreign key will never occur. Instead, if the VendorID value is invalid, the THROW statement will raise a custom error that provides a user-friendly message.
In this case, the custom error contains a short message that indicates that the VendorID is not valid.
The above calling script attempts to insert a row into the Invoices
table with a VendorID of 799. Since this VendorID doesn't exist in the Vendors table, the INSERT statement causes the custom error to be raised. As a result, program execution jumps into the CATCH block of the TRY...CATCH statement. This CATCH block prints a message that indicates that an error occurred, and it prints the message that's stored in the custom error. Then, the catch block uses an IF statement to check if the error number is greater than or equal to 50000. If so, it prints a message on the third line that indicates that the error is a custom error.
RAISERROR statement to raise errors. This statement lets you raise system errors that terminate the client connection instead of allowing execution to jump into a CATCH block. Because you shouldn't raise system errors, though, you can simplify your error handling by always using the THROW statement. Then, you can always use a CATCH block to handle the error.
The following is an example of a stored procedure that assist in the INSERT of new data.
CREATE PROCEDURE spInsertCustomer
@firstName NVARCHAR(50),
@lastName NVARCHAR(50),
@email NVARCHAR(50),
@phone NVARCHAR(50)
AS
BEGIN
INSERT INTO Customers
(firstName, lastName, email, phone)
VALUES
(@firstName, @lastName, @email, @phone);
END
The following is an example of a stored procedure that assist in the UPDATE of existing data.
CREATE PROCEDURE spUpdateCustomer
@customerID INT,
@firstName NVARCHAR(50),
@lastName NVARCHAR(50),
@email NVARCHAR(50),
@phone NVARCHAR(50)
AS
BEGIN
UPDATE Customers
SET
firstName = @firstName,
lastName = @lastName,
email = @email,
phone = @phone
WHERE customerID = @customerID;
END
And although rarely used, here is an example of a DELETE stored Procedure!
CREATE PROCEDURE spDeleteCustomer
@customerID INT
AS
BEGIN
DELETE FROM Customers
WHERE customerID = @customerID;
ENDIn addition to the single-line, and aggregate functions, you've learned about previously, you can also create your own functions, called user-defined functions. To do that, you use code that's similar to the code you use to create a stored procedure. There are some clear differences between stored procedures and user-defined functions.
Like a stored procedure, a UDF can accept one or more input parameters. However, a UDF can't be defined with output parameters. Instead, a RETURN statement must be used to return a value to the calling code. The value that's returned must be compatible with the data type that's specified in the RETURNs clause.
Functions can be used inline within other scripts. The way this is done depends on the type of function you can create. There are three kinds of functions that can be created:
Scalar-valued functions return a single value. They can be used inline within a SELECT statement, or within a stored procedure or trigger. The following is an example of a scalar-valued function that returns the full name of a customer:
CREATE OR ALTER FUNCTION fncGetCustomerFullName
(@customerID INT)
RETURNS NVARCHAR(100)
BEGIN
DECLARE @fullName NVARCHAR(100);
SELECT @fullName = firstName + ' ' + lastName
FROM Customers
WHERE customerID = @customerID;
RETURN @fullName;
ENDScalar-valued functions can be used inline within a SELECT statement. The following is an example of how to use the above function:
SELECT
customerID,
dbo.fncGetCustomerFullName(customerID) AS FullName
FROM Customers;
SELECT statement, in which case it's called a simple table-valued function, or it can be based on two or more statements, in which case it's called a multi-statement table-valued function,INSERT, UPDATE, or DELETE statement against the database,SELECT statement,The following is the general syntax used when creating a user-defined function:
CREATE FUNCTION [schema_name.]function_name (
[@parameter_name data_type [= default]] [, ...])
RETURNS data_type
[AS]
BEGIN
[sql_statements]
RETURN scalar_expression
ENDIn most default databases, the normal default schema would be "dbo", which is short for database owner.
Table-valued functions return an entire table. They can be used inline within a SELECT statement, or within a stored procedure or trigger. The following is an example of a table-valued function that returns a list of customers:
CREATE OR ALTER FUNCTION fncGetCustomers
RETURNS TABLE
AS
RETURN
SELECT
customerID,
firstName,
lastName,
email,
phone
FROM Customers
ORDER BY lastName, firstName;Table-valued functions can be used inline within a SELECT statement. The following is an example of how to use the above function:
SELECT
customerID,
firstName,
lastName,
email,
phone
FROM dbo.fncGetCustomers();Multi-statement table-valued functions return an entire table. They can be used inline within a SELECT statement, or within a stored procedure or trigger. The following is an example of a multi-statement table-valued function that returns a list of customers:
CREATE OR ALTER FUNCTION fnGetCustomers
RETURNS @Customers TABLE
(
customerID INT,
firstName NVARCHAR(50),
lastName NVARCHAR(50),
email NVARCHAR(50),
phone NVARCHAR(50)
)
AS
BEGIN
INSERT INTO @Customers
SELECT
customerID,
firstName,
lastName,
email,
phone
FROM Customers
ORDER BY lastName, firstName;
RETURN;
ENDMulti-statement table-valued functions can be used inline within a SELECT statement. The following is an example of how to use the above function:
SELECT
customerID,
firstName,
lastName,
email,
phone,
vendorID
FROM dbo.fnGetCustomers();An example of where a table-valued UDF can be used in a JOIN statement!
SELECT
i.InvoiceID,
i.InvoiceNumber,
i.InvoiceDate,
i.InvoiceTotal,
v.VendorName
FROM Invoices i
JOIN dbo.fnGetVendors() v ON i.VendorID = v.VendorID;
To DROP a function in SQL Server, the following script can be used:
DROP FUNCTION IF EXISTS <function name>;User-Defined Functions can be executed using the SELECT statement. The following is an example of how to execute the fnGetCustomerFullName function:
SELECT dbo.fnGetCustomerFullName(1);