70-461. Chp 13. Designing And Implementing T-Sql Routines

Front 1

Name three kinds T-SQL routines

Back 1

stored procedures, triggers, and user-defined functions.

Front 2

Define Stored Procedures

Back 2

routines that reside in a database and encapsulate code

Front 3

List the types of stored procedures that SQL Server permits

Back 3

SQL Server permits several types of stored procedures, such as the following:

■ T-SQL stored procedures written in T-SQL code

■ CLR stored procedures stored as Microsoft .NET assemblies in the database

■ Extended stored procedures, which make calls to externally compiled data definition languages (DLLs)

Front 4

How should the NOCOUNT setting be used when creating stored procedures? Why?

Back 4

Exam Tip

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 that message to the client. In addition, SET NOCOUNT ON can improve the performance of frequently executed stored procedures because there is less network communication required when the "rows(s) affected" message is not returned to the client.

Front 5

What's the purpose of the RETURN command?

Back 5

RETURN stops the execution of the procedure and returns control back to the caller. Statements after the RETURN statement are not executed.

Front 6

When should the EXECUTE command by used when calling a STORED PROCEDURE?

Back 6

Always include the EXEC command when calling a stored procedure. That will avoid getting unexpected and confusing errors. If the statement is no longer the first statement in the batch, it will still run.

Front 7

When passing parameters to a STORED PROCEDURE, should they be called by name OR passed in order listed in the CREATE PROCEDURE statement?

Back 7

It is a best practice to name the parameters when you call stored procedures. Although passing parameter values by position may be more compact, it is also more error prone. If you pass parameters by name and the parameter order changes in the stored procedure,your call of the procedure will still work.

Front 8

What is the syntax for retrieving data from a stored procedure OUTPUT parameter?

Back 8

To retrieve data from the output parameter, you must use the keyword OUTPUT when you call the stored procedure, and you must provide a variable to capture the value when it comes back.

For example -

DECLARE @rowsreturned AS INT;

EXEC Sales.GetCustomerOrders

@custid = 37,

@orderdatefrom = '20070401',

@orderdateto = '20070701',

@numrows = @rowsreturned OUTPUT;

SELECT @rowsreturned AS 'Rows Returned';GO

Front 9

List the T-SQL control flow statements.

Back 9

■IF/ELSE

■ WHILE (with BREAK and CONTINUE)

■ WAITFOR

■ GOTO

■ RETURN (normally inside T-SQL routines)

Front 10

How can an "infinite loop" be avoided?

Back 10

When you code a WHILE loop, it is critical to ensure that something happens in the loop that will eventually make the WHILE condition evaluate to false so that the WHILE loop will terminate. Always check the body of the WHILE loop and make sure that a counter is incremented or a value changes so that the loop will always terminate under all conditions.

Front 11

Is a BEGIN/END block optional or required in a WHILE loop?

Back 11

EXAM TIP

A BEGIN/END block is optional in a WHILE loop if you only have one statement, it is a best practice to include it. The BEGIN/END block helps you organize your code,makes it easier to read, and makes it easier to modify in the future.

Any statement block in a WHILE loop with more than one statement requires the BEGIN/END construct.

Front 12

What are the three WAITFOR options?

Back 12

WAITFOR DELAY

WAITFOR TIME

WAITFOR RECEIVE

Front 13

What is the purpose of the GOTO statement?

Back 13

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.

Front 14

What types of results can STORED PROCEDURES return?

Back 14

- Result sets based on a query

- values in OUTPUT parameters

- return codes sent back from RETURN statements

Front 15

What are the two types of parameters for a T-SQL stored procedure?

Back 15

A T-SQL stored procedure can have input and output parameters.

Front 16

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

Back 16

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

Front 17

A STORED PROCEDURE can consist of up to how many batches of T-SQL code?

Back 17

1

Front 18

Can a STORED PROCEDURE call another STORED PROCEDURE?

Back 18

Yes

Front 19

How many results sets can a STORED PROCEDURE return to the caller?

Back 19

Stored procedures can return more than one result set to the caller.

Front 20

Which of the following T-SQL statements can be used to cause branching within astored procedure? (Choose all that apply.)

A. WHILE

B. BEGIN/END

C. IF/ELSE

D. GO

Back 20

Correct Answers: A and C

A. Correct: A WHILE statement starts a looping structure.

B. Incorrect: BEGIN and END do not cause branching. They are only used to groupstatements together.

C. Correct: IF and ELSE cause code execution to branch based on a condition in theIF clause.

D. Incorrect: A GO statement is just a batch terminator. It has no effect on codeexecution as such.

Front 21

A stored procedure calls another stored procedure. The calling stored procedure has created temporary tables, declared variables, and passes parameters to the called stored procedure. What data can the called stored procedure see from the caller?

A. The called procedure can see the variables, temporary tables, and passed parameters of the caller.

B. The called procedure can see the temporary tables but not the variables and passed parameters of the caller.

C. The called procedure can see the passed parameters and temporary tables but not the variables of the caller.

D. The called procedure cannot see any objects created by the calling procedure.

Back 21

Correct Answer: C

A. Incorrect: The variables of the calling procedure cannot be seen by the calledprocedure.

B. Incorrect: Temporary tables are visible, but passed parameters are also visible.

C. Correct: Both passed parameters and temporary tables are visible to called storedprocedures.

D. Incorrect: The called procedure can see temporary tables and passed parametersfrom the calling procedure.

Front 22

3. How can you use output parameters in T-SQL stored procedures? (Choose all that apply.)

A. You can pass data into a procedure by using an output parameter, but you cannot receive information back from it.

B. You can pass data into a procedure by using an output parameter, and any change made to the parameter will be passed back to the calling routine.

C. You cannot pass data into a procedure by using an output parameter; it is only used for passing data back to the caller.

D. You cannot pass data into a procedure by using an output parameter, nor can you receive data back from a procedure from an output parameter.

Back 22

Correct Answer: B

A. Incorrect: You can use an output parameter to receive information back from astored procedure.

B. Correct: You can both pass data into a stored procedure and retrieve informationback from it, by using an output parameter.

C. Incorrect: An output parameter is not used only for passing data back to the callerof the stored procedure. It is also used to pass data from the caller to a storedprocedure.

D. Incorrect: You can both pass data into a stored procedure and retrieve informationback from it, by using an output parameter.

Front 23

What is a TRIGGER?

Back 23

A trigger is a special kind of stored procedure that is associated with selected data manipulationlanguage (DML) events on a table or 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’scode runs.

Front 24

What is a DML TRIGGER?

Back 24

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.

Front 25

What are the two kinds of T_SQL DML TRIGGERS?

Back 25

AFTER This trigger fires after the event it is associated with finishes and can only be defined on permanent tables.

INSTEAD OF This trigger fires instead of the event it is associated with and can be defined on permanent tables and views.

Front 26

Exam Tip

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.

Back 26

na

Front 27

Exam Tip

It is not a good practice to return result sets from triggers. In SQL Server 2012 and earlier versions, returning a rowset from a trigger is allowed, but it cannot be relied on. You can also disable it with the sp_configure option called Disallow Results From Triggers. In addition, the ability to return result sets from a trigger is deprecated and will be dropped in the next version of SQL Server after SQL Server 2012.

Back 27

na

Front 28

describe usage of @@ROWCOUNT

Back 28

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

Front 29

Describe the usage of INSTEAD OF triggers.

Back 29

The INSTEAD OF trigger executes a batch of T-SQL code instead of the INSERT, UPDATE, or DELETE statement.

Front 30

What are the two types of DML triggers that can be created?

Back 30

You can create AFTER and INSTEAD OF DML-type triggers.

Front 31

If an AFTER trigger discovers an error, how does it prevent the DML command from completing?

Back 31

An AFTER trigger issues a THROW or RAISERROR command to cause the transactionof the DML command to roll back.