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 link languages (DLLs).

Question 3

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

Answer 3

1. Called from T-SQL code by using the EXECUTE command
2. Passing data to them through input parameters, and receive data back through output parameters
3. Returning result sets of queries to the client application
4. Modifying data in tables
5. Creating, Altering, and Droping 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. The following commands cannot be used in a Stored Procedure
   
   1. USE < database name >
   2. CREATE AGGREGATE
   3. RULE
   4. DEFAULT
   5. CREATE FUNCTION
   6. TRIGGER
   7. PROCEDURE
   8. VIEW statements.

Note that with a stored procedure the create, alter, and drop statements can be used. They can affect tables and indexes 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 helps improve performance by creating execution plans at the time the procedure is compiled 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

The CREATE command will fail.

If the stored procedure exists, it can be altered or dropped.

If a stored procedure that does not exist has an ALTER command issued, the ALTER command will fail.

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

Use either CREATE PROCEDURE or use the abbreviation CREATE PROC.

CREATE PROC Sales.GetCustomerOrders

(

@custid AS INT,

@orderdatefrom AS DATETIME = ‘19000101’,

@numrows AS INT = 0 OUTPUT

)

AS BEGIN

RETURN (OUTPUT)

END

Parameters are optional. If there is no default initialization, the parameter is required.

In the previous code, @custid is a required parameter.

If a default initialization is provided, 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.

The code in a stored procedure is surrounded 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

The setting NOCOUNT ON inside a stored procedure removes messages like (3 row(s) affected) from 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.

More than one return command can be used 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 not reliable - so you should not use them.

Use the SQL Server error numbers from @@ERROR or from ERROR_NUMBER() in a CATCH block instead.

To send your own return codes back to the caller, inserti 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, use EXEC followed by the stored procedure name, 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.

If a 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

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

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.

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.

The optional ELSE is used 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 the WHILE loop, 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, it causes the 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.

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.

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, in 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 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

Can you call a stored procedure from another stored procedure?

Answer 21

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 calls Proc 1. Also, variables declared in Proc1 and Proc1’s parameters are not visible to any of the procedures called by Proc1.

Question 22

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

Answer 22

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

Question 23

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

Answer 23

Yes.

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

Question 24

What is a trigger?

Answer 24

A trigger is a special kind of stored procedure that is associated with selected DML events on a table or a view.

A trigger is not explicitly executed.

Rather, a trigger is fired when a DML event occurs that the trigger is associated with, such as INSERT, UPDATE, or DELETE event.

Whenever the event takes place, the trigger fires and the trigger’s code runs.

Question 25

With what types of events can triggers be associated with?

Answer 25

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 26

What is a DML trigger?

Answer 26

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. A DML triggers can be used 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. NOTE: The schema of the trigger must be the same as the schema of the table or view to which the trigger is associated.

Question 27

What are the two types of DML triggers?

Answer 27

SQL Server supports two kinds of DML triggers: 1. **AFTER** - This trigger can Only be defined on Permanent Tables, and fires after the event it is associated with finishes. 2. **INSTEAD OF** - This trigger can be defined on Views and Permanent Tables, and fires instead of the event with which it is associated.

Question 28

Where does a DML trigger fit as far as transactions?

Answer 28

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, as well as, changes in the original DML statement. 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 29

How do you exit a trigger?

Answer 29

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

Question 30

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

Answer 30

For both types of DML triggers, there is access to 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 31

What is an AFTER trigger?

Answer 31

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 DML statement fails and therefore the trigger is not executed.

Question 32

What's an example of an AFTER trigger declaration?

Answer 32

CREATE TRIGGER Sales.tr\_SalesOrderDetailsDML ON Sales.OrderDetails AFTER DELETE, INSERT, UPDATE AS BEGIN END;

Question 33

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

Answer 33

When an UPDATE or DELETE occurs and no rows are affected and @@ROWCOUNT = 0. Since there is no point in proceeding with the trigger, first test @@ROWCOUNT \> 0. This must be 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 34

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

Answer 34

No. 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 35

Can AFTER triggers be nested?

Answer 35

Yes. There can be 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. Check the setting by using the sp\_configure stored procedure 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 36

What is an INSTEAD OF trigger?

Answer 36

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 37

What is the UPDATE() DML trigger function?

Answer 37

There are two functions in triggers to get information about what is going on: UPDATE() - Use this function to determine whether a particular column has been referenced by an INSERT or UPDATE statement. For example, use the following inside the trigger: IF UPDATE(qty) PRINT 'Column qty affected'; This 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 38

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

Answer 38

EXEC sp\_configure 'nested triggers', 0; RECONFIGURE;

Question 39

What are user defined functions?

Answer 39

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 40

What are the different types of UDFs?

Answer 40

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 41

What abbreviation do you use when you reference a UDF in the OBJECT\_ID() function?

Answer 41

FN - SQL scalar function, IF - SQL inline table valued function TF - SQL table-valued function. IF OBJECT\_ID('Sales.fn\_extension', 'FN') IS NOT NULL DROP FUNCTION Sales.fn\_extension; GO

Question 42

What are scalar UDFs?

Answer 42

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 43

What is a inline table-valued UDFs?

Answer 43

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 44

What is a multi-statement table-valued UDF?

Answer 44

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 45

What are the limitations of UDFs?

Answer 45

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 46

What does the ENCRYPTION option do?

Answer 46

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

Question 47

What does the SCHEMABINDING option do?

Answer 47

This binds the schemas of all referenced objects.

Question 48

What does the RETURNS NULL ON NULL INPUT option do?

Answer 48

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

Question 49

What does the CALLED ON NULL INPUT option do?

Answer 49

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

Question 50

What does the EXECUTE AS option do?

Answer 50

This executes under various contexts.