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 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.
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 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;
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'; 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.
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;
How do you exit a trigger?
The normal exit from a trigger is to use the RETURN statement, just as in a stored procedure.
How do you access the rows that were affected by the modification that cause the trigger to fire?
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.
What is an AFTER trigger?
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.
What’s an example of an AFTER trigger declaration?
CREATE TRIGGER Sales.tr_SalesOrderDetailsDML ON Sales.OrderDetails AFTER DELETE, INSERT, UPDATE AS BEGIN
How can you short circuit a trigger when no rows are affected?
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.
Is it a good practice to return result sets from triggers?
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”.
Can AFTER triggers be nested?
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.
What is an INSTEAD OF trigger?
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.
What is the UPDATE() DML trigger function?
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.
How can you turn off nested triggers on a SQL Server instance by using T-SQL?
EXEC sp_configure ‘nested triggers’, 0;
What are user defined functions?
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.
What are the different types of UDFs?
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.
What abbreviation do you use when you reference a UDF in the OBJECT_ID() function?
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;
What are scalar UDFs?
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.
What is a inline table-valued UDFs?
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.
What is a multi-statement table-valued UDF?
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;
What are the limitations of UDFs?
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.
What does the ENCRYPTION option do?
As with stored procedures and triggers, this is really an obfuscation of the source code and not a complete encryption.
What does the SCHEMABINDING option do?
This binds the schemas of all referenced objects.
What does the RETURNS NULL ON NULL INPUT option do?
If this is set, any NULL parameters cause a scalar UDF to return NULL without executing the body of the function.
What does the CALLED ON NULL INPUT option do?
This is the default, and it implies that a scalar function body will execute even if NULL is passed as a parameter.
What does the EXECUTE AS option do?
This executes under various contexts.