Flashcards in Chapter 13 Designing and Implementing T-SQL Routines Deck (51)
What is a stored procedure?
Stored procedures are routines that reside in a database and encapsulate code.
What types of stored procedures does SQL Server permit?
(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).
What are the important features of a T-SQL stored procedure?
(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.
How many batches does a T-SQL stored procedure consist of?
A T-SQL stored procedure consists of a single batch of T-SQL code.
What T-SQL statements cannot be used in a T-SQL stored procedure?
(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.
How can a T-SQL stored procedure make a database more secure?
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.
How can a T-SQL stored procedure present a more versatile data access layer to users and applications?
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.
How can a T-SQL stored procedure help improve performance?
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.
What happens when you try to create a stored procedure and it already exists?
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;
How do you create stored procedure?
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
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.
What does the NOCOUNT ON setting do?
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.
What does the RETURN statement do in the context of a stored procedure?
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.
How do you execute a stored procedure?
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:
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.
How do you pass parameters to a stored procedure when it is invoked?
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';
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.
How do you use output parameters in a stored procedure?
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
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;
@orderdatefrom = '20070401',
@orderdateto = '20070701',
@numrows = @rowsreturned OUTPUT;
What are the IF/ELSE statements?
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.'
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.
What is the WHILE statement?
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.
SET @count += 1
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.
What is the WAITFOR command?
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';
What is the GOTO construct?
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';
PRINT 'Second PRINT statement';
It is not recommended to use the GOTO statement because it can quickly lead to code that is complex and convoluted.
Can a stored procedure return multiple result sets back to a client?
Besides result sets, what are the other types of results that a stored procedure can return?
Stored procedures can return values in OUTPUT parameters and return codes sent back from the RETURN statement.
Can you call a stored procedure from another stored procedure?
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.
Can a stored procedure span multiple batches of T-SQL code?
No. A stored procedure can only contain one batch of T-SQL code.
Can an OUTPUT parameter both pass data into a stored procedure and retrieve information back from it?
Yes. You can both pass data into a stored procedure and retrieve information back from it, by using an output parameter.
What is a trigger?
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.
With what types of events can triggers be associated with?
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.
What is a DML trigger?
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.
What are the two types of DML triggers?
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.
Where does a DML trigger fit as far as transactions?
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;