Chapter 13 Designing and Implementing T-SQL Routines

Question 1

What is a stored procedure?

Answer 1

Stored procedures are routines that reside in a database and encapsulate code.

Question 2

What types of stored procedures does SQL Server permit?

Answer 2

(1) T-SQL stored procedures written in T-SQL code, (2) CLR stored procedures stored as .NET assemblies in the database, (3) Extended stored procedures, which make calls to externally compiled data definition languages (DLLs).

Question 3

What are the important features of a T-SQL stored procedure?

Answer 3

(1) They can be called from T-SQL code by using the EXECUTE command, (2) You can pass data to them through input parameters, and receive data back through output parameters, (3) They can return result sets of queries to the client application. (4) They can modify data in tables, (5) They can create, alter, and drop tables and indexes.

Question 4

How many batches does a T-SQL stored procedure consist of?

Answer 4

A T-SQL stored procedure consists of a single batch of T-SQL code.

Question 5

What T-SQL statements cannot be used in a T-SQL stored procedure?

Answer 5

(1) You cannot use the USE < database name > command, (2) You cannot use CREATE AGGREGATE, RULE, DEFAULT, CREATE FUNCTION, TRIGGER, PROCEDURE, or VIEW statements.

Note that you can however create, alter, and drop a table and an index by using the CREATE, ALTER, and DROP statements.

Question 6

How can a T-SQL stored procedure make a database more secure?

Answer 6

Rather than give the user access to database tables directly, you can grant permissions to a stored procedure.

Stored procedures can also help prevent SQL injection attacks by parameterizing dynamic SQL.

Question 7

How can a T-SQL stored procedure present a more versatile data access layer to users and applications?

Answer 7

The stored procedure allows the user to bypass complex logic to get desired results.

Underlying physical structures of database tables may change and the stored procedure may be modified, but because the user sees the same procedure and parameters, the user does not need to know about the changes.

Question 8

How can a T-SQL stored procedure help improve performance?

Answer 8

A T-SQL stored procedure can help improve performance by creating execution plans that can be reused. By passing in parameters, you can reuse the cached plan of a stored procedure for many different parameter values, preventing the need to recompile the T-SQL code.

Stored procedures can reduce network traffic too. If the application had to do all the work, intermediate results would have to be passed back to the application over the network. Similarly, if the application does all the work, it must send every T-SQL command to the SQL Server over the network.

Question 9

What happens when you try to create a stored procedure and it already exists?

Answer 9

Your CREATE command will fail. If the stored procedure already exists, you can alter the stored procedure, but if you try to alter a stored procedure that does not exist, the ALTER command will fail.

You need to place a conditional DROP of the stored procedure before trying to create it.

IF OBJECT_ID(‘Sales.GetCustomerOrders’, ‘P’) IS NOT NULL
DROP PROC Sales.GetCustomerOrders;
GO

Question 10

How do you create stored procedure?

Answer 10

You can write either CREATE PROCEDURE or use the abbreviation CREATE PROC when creating the stored procedure.

CREATE PROC Sales.GetCustomerOrders
   @custid AS INT,
   @orderdatefrom AS DATETIME = '19000101',
   @numrows AS INT = 0 OUTPUT
AS
BEGIN
   --TODO
   RETURN;
END

You don’t have to put parameters in a stored procedure, but if you want to add them, they must be listed right after the beginning of the procedure.

Parameters can be required or optional.

If you don’t provide a default initialization, the parameter is required. In the previous code, @custid is a required parameter.

If you do provide a default initialization, the parameter is optional. If an optional parameter is not given a value when the procedure is called, the default value will be used in the rest of the procedure.

The OUTPUT keyword specifies a special parameter that returns values back to the caller. Output parameters are always optional parameters.

The AS command is required after the list of parameters.

You can surround the code in a stored procedure by using a BEGIN/END block. Though this is not required, using a BEGIN/END block can help clarify the code.

Question 11

What does the NOCOUNT ON setting do?

Answer 11

You can embed the setting of NOCOUNT to ON inside the stored procedure to remove messages like (3 row(s) affected) being returned every time the procedure executes.

The NOCOUNT setting of ON or OFF stays with the stored procedure when it is created. Placing a SET NOCOUNT ON at the beginning of every stored procedure prevents the procedure from returning messages to the client. It can also improve the performance of frequently executed stored procedures because there is less network communication required when the message is not returned from the client.

Question 12

What does the RETURN statement do in the context of a stored procedure?

Answer 12

A stored procedure normally ends when the T-SQL batch ends, but you can cause the procedure to exit at any point by using the RETURN command. You can use more than one return command in a procedure. RETURN stops the execution of the procedure and returns control back to the caller. Statements after the RETURN statement are not executed.

RETURN by itself causes SQL Server to send a status code back to the caller. The statuses are 0 for successful and a negative number if there is an error. However, the error numbers are no reliable - so you should not use them. Use the SQL Server error numbers from @@ERROR or from ERROR_NUMBER() in a CATCH block instead.

You can send your own return codes back to the caller by inserting an integer value after the RETURN statement. However, if you want to send information back to the caller, it is considered best practice to use an OUTPUT parameter.

Question 13

How do you execute a stored procedure?

Answer 13

Use the EXECUTE statement (or EXEC for short). If a stored procedure does not have any input parameters, you use EXEC followed by the stored procedure name, as in the following:

EXEC sp_configure

NOTE: System stored procedures in the master database can be executed from any database.

If the execution of a stored procedure is the first statement in a batch of T-SQL code or the only statement selected in a query window, you do not need the EXEC statement. However, if the stored procedure is the second or later statement, you must precede it with EXEC or EXECUTE.

It is a best practice to always include the EXEC command when calling a stored procedure. That will avoid getting unexpected errors.

Question 14

How do you pass parameters to a stored procedure when it is invoked?

Answer 14

When the stored procedure has input parameters, you can pass in a parameter value either by putting it in the correct position or by associating the value with the name of the parameter.

EXEC Sales.GetCustomerOrders 37, ‘20070401’, ‘20070701’;

or

EXEC Sales.GetCustomerOrders @custid = 37, @orderdatefrom = ‘20070401’, @orderdateto = ‘20070701’

When you pass the parameter values by using the parameter names, you can put the named parameters in any order. However, when you pass the parameter values by position, you must use the exact position of the parameters as defined in the CREATE PROCEDURE statement.

It is a best practice to name the parameters when you call stored procedures. If you pass parameters by name and the parameter order changes in the stored procedure, your call of the procedure will still work.

Question 15

How do you use output parameters in a stored procedure?

Answer 15

To use output parameters, you add the keyword OUTPUT (which can be abbreviated as OUT) after the parameter when you declare it in the CREATE PROC statement.

CREATE PROC Sales.GetCustomerOrders
...
@numrows AS INT = 0 OUTPUT
AS
...

To retrieve data from the output parameter, you must also use the keyword OUTPUT when you call the stored procedure and you must provide a variable to capture the value when it comes back. (If you don’t have the OUTPUT keyword in the procedure value, no value will be returned in the variable)

DECLARE @rowsreturned INT;
EXEC Sales.GetCustomerOrders
@custid =37,
@orderdatefrom = '20070401',
@orderdateto = '20070701',
@numrows = @rowsreturned OUTPUT;

Question 16

What are the IF/ELSE statements?

Answer 16

The IF/ELSE construct gives you the ability to conditionally execute code. You enter an expression after the IF keyword and if the expression evaluates to TRUE, the statement or block of statements after the IF statement will be executed. You can use the optional ELSE to add a different statement or block of statements to be executed if the expression evaluates to false.

IF @var1 = @var2
PRINT ‘The variables are equal.’
ELSE
PRINT ‘The variables are not equal.’

When the IF/ELSE statements are used without the BEGIN/END blocks, they each only deal with one statement.

Question 17

What is the WHILE statement?

Answer 17

With the WHILE construct, you can create loops inside T-SQL in order to execute a statement block as long as a condition continues to evaluate to true. You can use the WHILE construct in cursors or you can use it by itself.

The keyword WHILE is followed by a condition that evaluates to either true or false. If the condition evaluates to true when it’s first tested, the control of execution enters the loop, finishes the commands in side the loop the first time, and then tests the condition again. Each time the loop is repeated, the WHILE condition is retested. As soon as the loop ends and execution control passes to the next statement following the WHILE loop.

WHILE @count
SET @count += 1
END

When you create a WHILE loop, it is critical to ensure that something happens in the loop that will eventually make it terminate. Always check the body of the WHILE loop to make sure that the counter is incremented or a value changes so that the loop will terminate.

Inside you WHILE loop, you can use a BREAK statement to end the loop immediately and a CONTINUE statement to cause execution to jump back to the beginning of the loop.

Question 18

What is the WAITFOR command?

Answer 18

The WAITFOR command does not change control flow or cause branching, but it can cause execution of statements to pause for a specified period of time.

WAITFOR has three options: WAITFOR DELAY, WAITFOR TIME, and WAITFOR RECEIVE (which is used only with Service Broker).

WAITFOR DELAY causes the execution to delay for a requested duration. For example, the following WAITFOR DELAY pauses the code execution for 20 seconds:

WAITFOR DELAY ‘00:00:20’;

WAITFOR TIME on the other hand, pauses the execution to wait for a specific time. For example, the following waits until 11:45:

WAITFOR TIME ‘23:46:00’;

Question 19

What is the GOTO construct?

Answer 19

With the GOTO construct, you can cause your code to jump to a defined T-SQL label. All the intervening T-SQL code is skipped when the jump occurs. For example, the following code, the second PRINT statement is skipped:

PRINT 'First PRINT statement';
GOTO MyLabel;
PRINT 'Second PRINT statement';
MyLable:
PRINT 'End';

It is not recommended to use the GOTO statement because it can quickly lead to code that is complex and convoluted.

Question 20

Can a stored procedure return multiple result sets back to a client?

Answer 20

Yes.

Question 21

Besides result sets, what are the other types of results that a stored procedure can return?

Answer 21

Stored procedures can return values in OUTPUT parameters and return codes sent back from the RETURN statement.

Question 22

Can you call a stored procedure from another stored procedure?

Answer 22

Yes, but you need to observe the following when calling other procedures:

If you create a temp table in one stored procedure, call it Proc1 - that temporary table is visible to all other stored procedures called from Proc1. However, that temporary table is not visible to any procedures that call Proc 1.

Also, variables declared in Proc1 and Proc1’s parameters are not visible to any of the procedures called by Proc1.

Question 23

Can a stored procedure span multiple batches of T-SQL code?

Answer 23

No. A stored procedure can only contain one batch of T-SQL code.

Question 24

Can an OUTPUT parameter both pass data into a stored procedure and retrieve information back from it?

Answer 24

Yes. You can both pass data into a stored procedure and retrieve information back from it, by using an output parameter.

Question 25

What is a trigger?

Answer 25

A trigger is a special kind of stored procedure that is associated with selected DML events on a table or a view. A trigger cannot be explicitly executed. Rather, a trigger is fired when a DML event occurs that the trigger is associated with, such as INSERT, UPDATE, or DELETE. Whenever the event takes place, the trigger fires and the trigger’s code runs.

Question 26

With what types of events can triggers be associated with?

Answer 26

SQL Server supports the association of triggers with two kinds of events:

(1) Data manipulation events (DML triggers)
(2) Data definition events (DDL triggers) such as CREATE TABLE.

Question 27

What is a DML trigger?

Answer 27

A DML trigger is a T-SQL batch associated with a table that is defined to respond to a particular DML event such as an INSERT, UPDATE, or DELETE or a combination of those events.

You can use DML triggers for auditing, enforcing complex integrity rules, and more.

A trigger executes only once for each DML statement no matter how many rows may be affected.

Also, the schema of the trigger must be the same as the schema of the table or view the trigger is associated with.

Question 28

What are the two types of DML triggers?

Answer 28

SQL Server supports two kinds of DML triggers:

(1) AFTER - This trigger fires after the event it is associated with finishes and can only be defined on permanent tables.
(2) INSTEAD OF - This trigger fires instead of the event it is associated with and can be defined on permanent tables and views.

Question 29

Where does a DML trigger fit as far as transactions?

Answer 29

Both types of DML triggers execute as part of the transaction associated with the INSERT, UPDATE, or DELETE statement. A trigger is considered part of the transaction that includes the event that caused the trigger to fire.

Issuing a ROLLBACK TRAN command within the trigger’s code causes a rollback of all changes that took place in the trigger, in addition to rolling back the original DML statement to which the trigger is attached.

However, using a ROLLBACK TRAN in a trigger can have some unwanted side effects. Instead, you can issue THROW or RAISERROR and control the failure by using your standard error handling routines, e.g.

THROW 50000, ‘Duplicate category names not allowed’, 0;

Question 30

How do you exit a trigger?

Answer 30

The normal exit from a trigger is to use the RETURN statement, just as in a stored procedure.

Question 31

How do you access the rows that were affected by the modification that cause the trigger to fire?

Answer 31

For both types of DML triggers, you can access tables that are named inserted and deleted. These tables contain the rows that were affected by the modification that caused the trigger to fire. The inserted table holds the new image of the affected rows in the case of INSERT and UPDATE statements. The deleted table holds the old image of the affected rows in the case of DELETE and UPDATE statements. In the case of INSTEAD OF trigger, the inserted and deleted tables contain the rows that would be affected b the DML statement.

Question 32

What is an AFTER trigger?

Answer 32

AFTER triggers can only be defined for tables. In an AFTER trigger, the trigger code executes after the DML statement has passed all constraints such as primary/foreign key constraints, unique constraints, or check constraints. If the constraint is violated, the statement fails and the trigger is not executed.

Question 33

What’s an example of an AFTER trigger declaration?

Answer 33

CREATE TRIGGER Sales.tr_SalesOrderDetailsDML
ON Sales.OrderDetails
AFTER DELETE, INSERT, UPDATE
AS
BEGIN

END;

Question 34

How can you short circuit a trigger when no rows are affected?

Answer 34

When an UPDATE or DELETE occurs and no rows are affected, there is no point in proceeding with the trigger. You can improve the performance of the trigger by testing whether @@ROWCOUNT is 0 in the very first line of the trigger. It must be the first line because @@ROWCOUNT will be set back to 0 by any additional statement. When the AFTER trigger begins, @@ROWCOUNT will contain the number of rows affected by the outer INSERT, UPDATE, or DELETE statement.

Question 35

Is it a good practice to return result sets from triggers?

Answer 35

No. It is not a good practice to return result sets from triggers. In SQL Server 2012 and earlier versions, returning a result set from a trigger is allowed, but it cannot be relied upon. You can also disable it with the sp_configure option called “Disallow Results From Triggers”.

Question 36

Can AFTER triggers be nested?

Answer 36

AFTER triggers can be nested - that is, you can have a trigger on Table A that updates Table B. Then Table B may have a trigger that is executed as well. The maximum depth of nested trigger executions is 32. If the nesting is circular, the maximum level of 32 will be reached and the trigger execution will stop.

Nested triggers is a configuration option for the entire SQL Server instance. It is on by default but you can disable it for the server. You can check the setting by using the sp_configure stored procedure, e.g.

EXEC sp_configure ‘nested triggers’;

To turn the nested triggers option off at the server level, issue the following command:

EXEC sp_configure ‘nested triggers’, 0;

You must then issue the RECONFIGURE statement to make the setting take place.

Question 37

What is an INSTEAD OF trigger?

Answer 37

The INSTEAD OF trigger executes a batch of T-SQL code instead of the INSERT, UPDATE, and DELETE statement. You can reissue the statement later in the code.

Although INSTEAD OF triggers can be created against both tables and views, they are commonly used with views. The reason is that when you send an UPDATE statement against a view, only one base table can be updated at a time. In addition, the view may have aggregations or functions on columns that prevent a direct update. An INSTEAD OF trigger can take that UPDATE statement against the view and instead of executing it, replace it with two or more UPDATE statements against the base tables of the view.

Question 38

What is the UPDATE() DML trigger function?

Answer 38

You can use two functions in your trigger code to get information about what is going on:

UPDATE() - You can use this function to determine whether a particular column has been referenced by an INSERT or UPDATE statement. For example, you can insert the following inside the trigger:

IF UPDATE(qty)
   PRINT 'Column qty affected';

The following statement would make UPDATE(qty) true:

UPDATE Sales.OrderDetails SET qty=99;

The UPDATE function returns true even if the column value is set to itself in an UPDATE statement. It is only testing whether the column is referenced.

Question 39

How can you turn off nested triggers on a SQL Server instance by using T-SQL?

Answer 39

EXEC sp_configure ‘nested triggers’, 0;

RECONFIGURE;

Question 40

What are user defined functions?

Answer 40

User defined functions (UDF) are T-SQL or CLR routines that can accept parameters and return either scalar values or tables.

The purpose of a UDF is to encapsulate reusable T-SQL code.

Like stored procedures, UDF’s can accept parameters and the parameters can be accessed inside the function as variables.

Unlike stored procedures, UDFs are embedded in T-SQL statements and they execute as part of a T-SQL command. UDFs cannot be executed by using the EXECUTE command.

UDFs can access SQL Server data, but they cannot perform any DDL and DML - that is, they cannot make modifications to tables, indexes, or other objects or change the data in tables.

Question 41

What are the different types of UDFs?

Answer 41

There are two major types of UDFs: scalar and table-valued. The scalar function returns a single value back to the caller, whereas the table-valued function returns a table.

A table-valued UDF with a single line of code is called an inline table-valued UDF. A table-valued UDF with multiple lines of code is called a multistatement table-valued UDF.

Question 42

What abbreviation do you use when you reference a UDF in the OBJECT_ID() function?

Answer 42

FN - SQL scalar function, IF - SQL inline table-valued function, TF - SQL table-valued function.

e.g.

IF OBJECT_ID(‘Sales.fn_extension’, ‘FN’) IS NOT NULL
DROP FUNCTION Sales.fn_extension;
GO

Question 43

What are scalar UDFs?

Answer 43

Scalar UDFs are called scalar because they return a single value. Scalar UDFs can appear anywhere in the query where an expression that returns a single value can appear (e.g. in the SELECT column list or WHERE clause).

All the code within the scalar UDF must be enclosed in a BEGIN/END block.

CREATE FUNCTION dbo.FunctionName
(
   @param1 int,
   @param2 int
)
RETURNS INT
AS
BEGIN
   RETURN @param 1 + @param 2
END

SELECT dbo.FunctionName(@param1, @param2)

*Note that scalar UDFs are never “inline”. Only table-valued UDFs can be inline.

Question 44

What is a inline table-valued UDFs?

Answer 44

A table-valued UDF returns a table rather than a single value to the caller. As a result, it can be called in a T-SQL query whenever a table is expected, such as in the FROM clause.

An inline table-valued function is the only type of UDF that can be written without a BEGIN/END block. It contains a single SELECT statement that returns a table.

CREATE FUNCTION dbo.FunctionName
(
   @param1 int,
   @param2 char(5)
)
RETURNS TABLE AS RETURN
(
   SELECT @param1 AS c1, @param2 AS c2
)

To call the function, embed it in the FROM clause of a SELECT statement.

Note that because an inline table-valued function does not perform any other operations, the optimizer treats it like a view. You can even use INSERT, UPDATE, and DELETE against it.

It’s this ability to return the results of a single SELECT that makes this an inline table-valued function.

Question 45

What is a multi-statement table-valued UDF?

Answer 45

In a multi-statement table-valued UDF, you must explicitly define the schema of a table to be returned as a table variable and then insert data into the table variable. The RETURN statement just ends the function and is not used to send any data back to the caller.

CREATE FUNCTION dbo.FunctionName
(
   @param1 int,
   @param2 char(5)
)
RETURNS @returntable TABLE
(
   c1 int,
   c2 char(5)
)
AS
BEGIN
   INSERT @returntable
      SELECT @param1, @param2
END;

Question 46

What are the limitations of UDFs?

Answer 46

The user creating the function needs CREATE FUNCTION privileges.

UDFs cannot do the following:

• • Apply any schema or data changes in the database.
• • Change the state of a database or SQL Server instance.
• • Create or access temporary tables
• • Call stored procedures
• • Execute dynamic SQL
• • Produce side effects such as RAND() or NEWID() functions which rely on information from the previous invocation.

Question 47

What does the ENCRYPTION option do?

Answer 47

As with stored procedures and triggers, this is really an obfuscation of the source code and not a complete encryption.

Question 48

What does the SCHEMABINDING option do?

Answer 48

This binds the schemas of all referenced objects.

Question 49

What does the RETURNS NULL ON NULL INPUT option do?

Answer 49

If this is set, any NULL parameters cause a scalar UDF to return NULL without executing the body of the function.

Question 50

What does the CALLED ON NULL INPUT option do?

Answer 50

This is the default, and it implies that a scalar function body will execute even if NULL is passed as a parameter.

Question 51

What does the EXECUTE AS option do?

Answer 51

This executes under various contexts.