Thought experiment answer
Skill 2.2 Create stored procedures
Now, say you need to use this key in the key of another table, which has three key columns also. Not only is this messy, but it begins to get rather confusing. While some manner of role-naming helps (changing Locality to DriversLicenceLocality, for example), using a surrogate key changes the table to something easier to follow:
Click here to view code image
CREATE TABLE Examples.DriversLicense (
DriversLicenseId int CONSTRAINT PKDriversLicense PRIMARY KEY,
Locality char(10) NOT NULL,
LicenseNumber varchar(40) NOT NULL,
CONSTRAINT AKDriversLicense UNIQUE (Locality, LicenseNumber)
);
CREATE TABLE Examples.EmployeeDriverLicense (
EmployeeDriverLicenseId int NOT NULL
CONSTRAINT PKEmployeeDriverLicense PRIMARY KEY,
EmployeeId int NOT NULL, --Ref to Employee table
DriversLicenseId int NOT NULL, --Ref to DriversLicense table
CONSTRAINT AKEmployeeDriverLicense UNIQUE (EmployeeId, DriversLicenseId)
);
It’s easier to read, and easier to understand where the key columns come from at a glance. It is important to realize that the DriversLicenseId actually represents the Locality and LicenseNumber in the EmployeeDriverLicense table. The meaning of the table doesn’t change, it is just implemented differently.
Need More Review? Primary Key Constraints
See the following article on MSDN for more details about creating PRIMARY KEY constraints https://msdn.microsoft.com/en-us/library/ms189039.aspx.
Skill 2.2 Create stored procedures
Stored procedures are coded objects that are used to bundle together Transact-SQL calls into one simple call from a client. There are, in SQL Server 2016, three versions of stored procedures. All of them are compiled and stored on the server, but each have different coding and execution limitations:
Interpreted SQL These objects are compiled into a format that are then interpreted, one line at a time, by the execution engine. This is the classic version of a stored procedure that has been around since version 1.0.
CLR (Common Language Runtime) These are procedures that are written in a .NET language and have a Transact-SQL calling mechanism.
Natively Compiled SQL These stored procedures are written in Transact-SQL, but are compiled into a C language module. They can only access memory optimized TABLE objects and other natively compiled objects. Natively compiled objects are similar to interpreted SQL stored procedures in appearance, but have many
limitations.
While each of the types of STORED PROCEDURE (and all coded object type) are different, they all look to Transact-SQL the same. You can run the code, or use the objects in queries in the same manner with few limitations.
This chapter is focused on interpreted SQL stored procedures, but it is important to understand what the CLR objects are. Using managed, .NET code you can create STORED PROCEDURE objects that behave and are called exactly like ones created in interpreted SQL. The .NET code used has access to everything that the interpreted code does as well.
These objects are not created using Management Studio in Transact-SQL, but are built in Visual Studio like other .NET programs.
Natively compiled objects are reviewed in Skill 3.4, but many of the topics in this section apply.
We also limit our focus to user-managed stored procedures in a user database. There are system stored procedures in the master database, and in the resource database, which is a special, hidden read-only database with additional system objects.
This section covers how to:
Design stored procedure components and structure based on business requirements
Implement input and output parameters Implement table-valued parameters Implement return codes
Streamline existing stored procedure logic
Implement error handling and transaction control logic within stored procedures
Design stored procedure components and structure based on business requirements
As this section moves forward, we cover several of the most important details about using stored procedures, parameters, and error handling. However, in this section, the focus is on why to use stored procedures, and a few examples that include one or more of the concepts that are covered in the rest of this section in more detail.
There are two common philosophies regarding the use of STORED PROCEDURE objects:
Using stored procedures as a complete encapsulation layer between the user and the database. This is a prevalent way of thinking for many architects (and a smoother path to using the new natively compiled stored procedures).
Using stored procedures only to encapsulate complex calls. This utilization is typical for people who use tools to generate their access layer, and only use stored
procedures when it is impossible to do from the user interface. Other uses are in building a report interface when simple ad-hoc queries do not suffice.
The biggest difference between the philosophies is that in the complete encapsulation version, you end up with a mix of very simple and very complex stored procedures. Hence we look at some simple procedures as well as an example of a complex one.
The form of a stored procedure is generally quite straightforward. The basic structure is:
Click here to view code image
CREATE PROCEDURE SchemaName.ObjectName [WITH options]
[FOR REPLICATION]
@Parameter1 datatype,
@Parameter2 datatype = 'Optional Default', @Parameter3 datatype = NULL
AS
1 or more Transact-SQL statements;
STORED PROCEDURE objects are schema-bound, just like TABLE objects, and all of the constraint types, Hence they follow the same rules for naming objects and name space, with the addition that STORED PROCEDURE object names should not be prefixed with SP, as this denotes a system-stored procedure that SQL Server checks for in the master database first.
Parameters are for sending data to, and receiving data from, a stored procedure. Any data type can be used for a parameter, and only very few have limitations (table-valued parameters must be read-only, and cursors can only be output parameters.) Parameters behave for the most part just like variables do in the body of the procedure, with values provided either by the caller or the default, including being changeable by the code of the stored procedure. If no default is provided, the parameter must be specified.
There are very few limitations on what Transact-SQL can be used in a stored procedure.
One example are statements that need to be the first statement in a batch, like CREATE SCHEMA, CREATE PROCEDURE, CREATE VIEW, etc. (and the ALTER versions of the same). You cannot use the USE command to change database context, and there are a few SET commands pertaining to query execution. However, using dynamic SQL (putting your query into a string variable and using EXECUTE or sp_executesql to run the code) allows you to run any code at all.
The options you can specify are:
WITH ENCRYPTION Encrypts the entry in sys.syscomments that contains the text of the STORED PROCEDURE create statement.
WITH RECOMPILE Specifies that a plan is not cached for the procedure, so it is recompiled for every execution.
WITH EXECUTE AS Let’s you change the security context that the procedure is executed under.
FOR REPLICATION Indicates that this is a procedure that is specifically created for replication.
Note The CREATE PROCEDURE statement
For more details about the CREATE PROCEDURE statement, check here on the MSDN site: https://msdn.microsoft.com/en-us/library/ms187926.aspx.
While we will not user the WITH EXECUTE AS clause in the stored procedure examples, it will be used when triggers are created in Skill 2.4.
In the first example, consider the business requirement that the user needs to be able to create, and remove rows from a simple table. If the table is defined as:
Click here to view code image
CREATE TABLE Examples.SimpleTable (
SimpleTableId int NOT NULL IDENTITY(1,1)
CONSTRAINT PKSimpleTable PRIMARY KEY, Value1 varchar(20) NOT NULL,
Value2 varchar(20) NOT NULL );
Then we could create the three stored procedures shown in Listing 2-1:
LISTING 2-1 Three simple stored procedures to insert, update, and delete data Click here to view code image
CREATE PROCEDURE Examples.SimpleTable_Insert
@SimpleTableId int, @Value1 varchar(20), @Value2 varchar(20) AS
INSERT INTO Examples.SimpleTable(Value1, Value2) VALUES (@Value1, @Value2);
UPDATE Examples.SimpleTable SET Value1 = @Value1,
Value2 = @Value2
WHERE SimpleTableId = @SimpleTableId;
GO
CREATE PROCEDURE Examples.SimpleTable_Delete @SimpleTableId int,
@Value varchar(20) AS
DELETE Examples.SimpleTable
WHERE SimpleTableId = @SimpleTableId GO
As you can see, the body of code for each STORED PROCEDURE is just one simple Transact-SQL query. The power of STORED PROCEDURE objects we are applying here is providing a strict user interface that you have complete control over.
There are three ways you can return data to a user from a stored procedure. Output parameters and return codes are covered later, but the first and most common is by using one or more result sets (Generally one result set is desirable. You can get the metadata of the first result set using the system stored procedure: sp_describe_first_result_set).
For example, you could create a stored procedure to return all of the data in the Examples.SimpleTable, ordered by Value1:
Click here to view code image
CREATE PROCEDURE Examples.SimpleTable_Select AS
SELECT SimpleTableId, Value1, Value2 FROM Examples.SimpleTable
ORDER BY Value1;
You can also return multiple result sets to the client, though again, it is generally desirable to return a single result set:
Click here to view code image
CREATE PROCEDURE Examples.SimpleTable_SelectValue1StartWithQorZ AS
SELECT SimpleTableId, Value1, Value2 FROM Examples.SimpleTable
WHERE Value1 LIKE 'Q%' ORDER BY Value1;
SELECT SimpleTableId, Value1, Value2 FROM Examples.SimpleTable
WHERE Value1 LIKE 'Z%' ORDER BY Value1 DESC;
Another ability is to return a variable number of result sets. For example, say the
requirement is to allow a user to search the table for rows where Value1 starts with ‘Q’ or
‘Z’ and the query is performed on a weekday. Whereas we could have done the INSERT or UPDATE statements from Listing 2-1, without the stored procedure you could not fulfill the business requirement in Transact-SQL code. For this (obviously contrived) requirement, you could write the following stored procedure which returns either 1 or 0 result sets:
Click here to view code image
CREATE PROCEDURE Examples.SimpleTable_SelectValue1StartWithQorZ AS
IF DATENAME(weekday,getdate()) NOT IN ('Saturday','Sunday') SELECT SimpleTableId, Value1, Value2
FROM Examples.SimpleTable WHERE Value1 LIKE '[QZ]%';
There are more elegant ways of accomplishing this requirement, particularly by
throwing an error to alert the user why nothing is returned, but we cover this later in this section. In the following sections covering the specific skills of writing a STORED PROCEDURE, the examples get more and more complex.
One interesting characteristic of STORED PROCEDURE code is that it can reference objects that do not exist. For example, you could code:
Click here to view code image
CREATE PROCEDURE Examples.ProcedureName AS
SELECT ColumnName From Bogus.TableName;
This compiles, regardless of the existence of Bogus.TableName. The compilation process of creating the procedure stores the reference by name and by internal id values when available. When it doesn’t have the internal id values, it tries to fetch them at
execution time. If the object does not exist at runtime, you get an error telling you “Invalid object name ‘Bogus.TableName’.”
One last fundamental about developing STORED PROCEDURE objects to understand.
Configurations you set on the connection using the SET command in the body of the STORED PROCEDURE only pertains to the statements in the STORED PROCEDURE.
The most common example is the SET NOCOUNT ON setting that suppresses the “Rows Affected” messages, but others such as ANSI WARNINGS can definitely be useful. So in the following procedure:
Click here to view code image
CREATE PROCEDURE Examples.SimpleTable_Select AS
SET NOCOUNT ON;
SELECT SimpleTableId, Value1, Value2 FROM Examples.SimpleTable
ORDER BY Value1;
If you perform this procedure, no “Rows Affected” would be returned, but if you did an INSERT statement in the same connection it would show the messages, unless you turned them off in the connection. However, silencing the messages from the calling connection silences them in STORED PROCEDURE calls. Because of this, you need to make sure and set any SET options that you rely on in the body of the object.
In this initial section, the main aspect of stored procedure writing we wanted to review was that stored procedures are just compiled batches of Transact-SQL code that you can use to do almost anything, allow you as the database developer to build a set of stored procedures to do almost any task.
Implement input and output parameters
Parameters allow you to pass data in and out of a STORED PROCEDURE object. To the code, they are very similar to variables, and can have their values changed as needed.
Whether or not you see the changes to the value of the parameters after running the procedure is based on how you declare, and then call, the STORED PROCEDURE.
The following examples use this table as reference.
Click here to view code image
CREATE TABLE Examples.Parameter (
ParameterId int NOT NULL IDENTITY(1,1) CONSTRAINT PKParameter PRIMARY KEY,
Value1 varchar(20) NOT NULL, Value2 varchar(20) NOT NULL, )
In the first iteration, it is simple input parameters for the two value columns:
Click here to view code image
CREATE PROCEDURE Examples.Parameter_Insert @Value1 varchar(20) = 'No entry given', @Value2 varchar(20) = 'No entry given' AS
SET NOCOUNT ON;
INSERT INTO Examples.Parameter(Value1,Value2) VALUES (@Value1, @Value2);
Now, in the following code block, we show the various ways you can run this code in Transact-SQL:
Click here to view code image --using all defaults
EXECUTE Examples.Parameter_Insert;
--by position, @Value1 parameter only
EXECUTE Examples.Parameter_Insert 'Some Entry';
--both columns by position
EXECUTE Examples.Parameter_Insert 'More Entry','More Entry';
-- using the name of the parameter (could also include
@Value2);
EXECUTE Examples.Parameter_Insert @Value1 = 'Other Entry';
--starting positionally, but finishing by name
EXECUTE Examples.Parameter_Insert 'Mixed Entry', @Value2 = 'Mixed Entry';
Once you start filling in parameters by name, you must continue to or you will receive an error. For example, if you attempt:
Click here to view code image
EXECUTE Examples.Parameter_Insert @Value1 = 'Remixed Entry', 'Remixed Entry';
It causes the following error:
Click here to view code image
Msg 119, Level 15, State 1, Line 736
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the
form '@name = value' has been used, all subsequent parameters must be passed in the form
'@name = value'.
If you want to get the value of a parameter after it has been modified in the STORED
PROCEDURE, you define the parameter as OUTPUT. In the alteration of the
Examples.Parameter_Insert stored procedure, use the UPPER and LOWER functions to modify the two @Value parameters, and then retrieve the value of SCOPE_IDENTITY() system function to get the key value of the ParameterId column that has been created:
Click here to view code image
ALTER PROCEDURE Examples.Parameter_Insert @Value1 varchar(20) = 'No entry given',
@Value2 varchar(20) = 'No entry given' OUTPUT, @NewParameterId int = NULL OUTPUT
AS
SET NOCOUNT ON;
SET @Value1 = UPPER(@Value1);
SET @Value2 = LOWER(@Value2);
INSERT INTO Examples.Parameter(Value1,Value2) VALUES (@Value1, @Value2);
SET @NewParameterId = SCOPE_IDENTITY();
Next we call the procedure using the variables as the parameter value. Set the
@NewParameterId value to a value that could not be returned. Note that the parameter is configured as OUTPUT on the EXECUTE statement as well (and if you try to declare a non-OUTPUT parameter as OUTPUT, it gives you an error telling you that the parameter is not an OUTPUT parameter).
Click here to view code image
DECLARE @Value1 varchar(20) = 'Test', @Value2 varchar(20) = 'Test', @NewParameterId int = -200;
EXECUTE Examples.Parameter_Insert @Value1 = @Value1,
@Value2 = @Value2 OUTPUT, @NewParameterId =
@NewParameterId OUTPUT;
SELECT @Value1 as Value1, @Value2 as Value2, @NewParameterId as NewParameterId;
SELECT *
FROM Examples.Parameter
WHERE ParameterId = @newParameterId;
This returns the following, showing that the @Value2 parameters values did change, as the row inserted has an all uppercase, and all lowercase versions of the parameter values.
However, the variable values did not change, unlike the @Value2 and @NewParameterId
values.
Click here to view code image
Value1 Value2 NewParameterId --- --- ---Test test 7
ParameterId Value1 Value2
--- ---7 TEST test
Using output parameters is the only way to return data to a variable that is not an integer, and is the only way to return more than one value to a variable, no matter the type.
Implement table-valued parameters
Table-valued parameters allow you need to pass more than a simple scalar value to a procedure. The reasons to do this generally fall into two categories:
The user wants to pick a set of rows to filter a set that could not easily be done using scalar valued parameters.
To create a procedure interface that allows you to create more than a single row in the table in a natural way.
For our example, we use a small table that has a few columns, and start with a few rows as well.
Click here to view code image
CREATE TABLE Examples.Machine (
MachineId int NOT NULL CONSTRAINT PKMachine PRIMARY KEY, MachineNumber char(3) NOT NULL CONSTRAINT AKMachine UNIQUE, Description varchar(50) NOT NULL
);
INSERT INTO Examples.Machine(MachineId, MachineNumber, Description)
VALUES (1,'001','Thing1'),(2,'002','Thing2'), (3,'003','Thing3');
Now, consider the case where the user wants to target a few rows. One method that was used for many years was to pass in a comma-delimited list. There were many ways of splitting the string, but SQL Server 2016 added a new system function STRING_SPLIT().
So we could pass a variable value such as ‘1,3’ to get three specific rows in the table.
Click here to view code image
CREATE PROCEDURE Examples.Machine_MultiSelect @MachineList varchar(200)
AS
SET NOCOUNT ON;
SELECT Machine.MachineId, Machine.MachineNumber FROM Examples.Machine
JOIN STRING_SPLIT(@MachineList,',') AS StringList ON StringList.value = Machine.MachineId;
While this works, and it still a very useful way of passing a pseudo-table valued parameter in cases where the caller is not able to use a table-valued parameter, the more efficient method of passing in a table of values is to pass a table object.
Note The SQL Server 2016 database engine
It is great to be up to date about the latest improvements in SQL Server 2016 database engine, particularly Transact-SQL, so if you see STRING_SPLIT(), you won’t be confused as to whether it is a trick question or not. The entire list of improvements in the database engine is located here:
https://msdn.microsoft.com/en-us/library/bb510411.aspx.
To use a table-valued parameter that is actually a true table object, we start by creating a USER DEFINED TYPE with the table data type. We use a generic structure, because this is a fairly generic usage to fetch a specific set of rows. Note that types are schema-owned, but they are not objects, so their names need not be different from the pool of objects.:
Click here to view code image
CREATE TYPE Examples.SurrogateKeyList AS table (
SurrogateKeyId int PRIMARY KEY --note you cannot name constraints for table types
);
Now you can use this table type as a parameter to the STORED PROCEDURE. You
Now you can use this table type as a parameter to the STORED PROCEDURE. You