Program Databases by Using T-SQL Flashcards
What are some things you can do with a stored procedure that you can’t do with a user-defined function?
Can you embed stored procedures in queries the way you can with functions?
Stored procedures allow you to modify data in the database, apply data definition changes to database objects, use temporary tables, dynamic SQL, and error handling.
No, you cannot embed a stored procedure in a query.
Syntax to execute a stored procedure with two input parameters
EXEC schema.procname @parameter1 = x, @parameter2 = y;
Syntax to execute a stored procedure with one input parameter and one output parameter
DECLARE @outputparameter AS datatype;
EXEC schema.procname @inputparameter, @outputparameter OUTPUT;
SELECT @outputparameter;
Syntax to create a stored procedure with one input parameter and one output parameter
CREATE PROC procname (@inputparameter AS datatype, @outputparameter AS datatype OUTPUT)
AS
SELECT….
How do you make input parameters optional?
You make input parameters optional by defining them with a default NULL:
@parameter AS datatype = NULL
What is dynamic SQL?
It’s a technique where you put together code as a character string, usually in a variable, and then tell SQL Server to execute the code that resides in that variable.
What are 2 ways to dynamically execute SQL statements?
What are 3 advantages of one over the other?
EXEC (‘sqlquery’) and sp_executesql
- EXEC doesn’t accept parameters, but sp_executesql does.
- sp_execute minimizes risk of SQL injection (security risk)
- sp_execute can perform better than EXEC because of query plan reuse
Syntax to use sp_executesql with 1 parameter.
DECLARE @sqlcode AS NVARCHAR(MAX) = N’SELECT … WHERE col1 = @param1’;
EXEC sys.sp_executesql
@stmt = @sqlcode
@params = N’@param1 AS datatype’
@param1 = something;
Syntax to declare and initialize two variables with one statement
DECLARE @variable1 datatype = x, @variable2 datatype = y;
What is the scope of variables?
Variables are always local to the batch in which they’re declared and go out of scope when the batch ends.
What are 3 ways of assigning values to variables?
- Initialize the variable with a value using the DECLARE statement
- Use SET statement, e.g. SET @variable = 1;
- Use a SELECT statement, e.g. SELECT @variable = COUNT(*) FROM tablename
Note: the query has to return exactly 1 row
Syntax for creating a synonym for a stored procedure
CREATE SYNONYM schema.procname FOR db.schema.procname;
What is a SYNONYM is SQL Server?
What types of things can a synonym point to?
How are synonyms useful?
A synonym is an alias or link to an object stored either on the same SQL server instance or a linked server.
A synonym can point to tables, views, procedures, and functions.
Synonyms can be used for referencing remote objects as though they were located locally, or for providing alternative names to other local objects.
RAISERROR function syntax
RAISERROR syntax to raise error using the following custom error:
sp_addmessage 50001, 10, N’This is an exception: %s %d %s’
How do you add error to event log?
Does RAISERROR terminate the batch or the transaction?
Part 1:
RAISERROR(N’%s %d’, severity, state, N’MessageText’, ErrorNum);
%s is placeholder for MessageText
%d is placeholder for ErrorNum (which is an integer)
severity is an int
state = 1
Example: RAISERROR(N’%s %d’, 10, 1, N’Custom error message number’, 2);
Returns: Custom error message number 2
Part 2:
sp_addmessage 50001, 10, N’This is an exception: %s %d %s’
RAISERROR(50001, 10, 1, N’Error number:’, 123, N’- No action needed’)
Returns: This is an exception: Error number: 123 - No action needed
Part 3:
Add WITH LOG, e.g.
RAISERROR(N’%s %d’, 10, 1, N’Custom error message number’, 2) WITH LOG;
Part 4: RAISERROR does not terminate the batch or the transaction
Where do you use THROW without parameters? What does it do (2 things)?
Syntax for THROW statement with parameters. Where can you use this? Describe its behavior when you use it outside a TRY-CATCH construct, inside a TRY block, in an open transaction with XACT_ABORT off, in an open transaction with XACT_ABORT on and not using TRY-CATCH, in an open transaction with XACT_ABORT on and using TRY-CATCH.
Errors raised by a THROW statement are always severity ___.
You use THROW without parameters in a CATCH block to re-throw the error that originally activated that CATCH block. It aborts the batch so any code below it doesn’t run.
THROW ErrorNum, ‘MessageText’, state;
Example: THROW 50001, ‘An Error Occurred’, 0;
You can use this inside or outside a CATCH block. When you use it:
- Outside a TRY-CATCH construct: it aborts the batch.
- Inside a TRY block: it causes the corresponding CATCH block to be activated.
- In an open tran with XACT_ABORT off: the transaction remains open and commitable
- In an open tran with XACT_ABORT on, no TRY-CATCH: it aborts the transaction
- In an open tran with XACT_ABORT on, with TRY-CATCH: it dooms the transaction
Errors raised by a THROW statement are always severity 16.
What is an advantage of using THROW over RAISERROR?
What can you do with RAISERROR that you can’t with THROW?
An advantage of using THROW over RAISERROR is that for custom errors, it does not require defining errors in the sys.messages table.
RAISERROR has the option to add error to event log while THROW does not.
Between THROW and RAISERROR, which one allows you to re-throw an original error that was caught by a TRY-CATCH construct?
THROW
THROW vs RAISERROR
Complete the following table
How can you add your own custom application errors?
Error codes above ______ are custom error codes.
Use the sp_addmessage procedure:
sp_addmessage ErrorNum, SeverityNum, ‘MessageText’
50,000
What does the @@ERROR system function do?
What should you do if you want to capture an error?
Returns last error code
Store @@ERROR in a variable
TRY-CATCH syntax
How does it work?
BEGIN TRY
….regular code…
END TRY
BEGIN CATCH
…error handling…
END CATCH;
If any error occurs in the regular code, execution is transferred to the CATCH block, and the error-handling code is executed.
What is one important limitation of TRY-CATCH?
What is the output of the following:
CREATE PROC inner_sp
AS
BEGIN TRY
PRINT ‘This prints’
SELECT * FROM NoSuchTable
END TRY
BEGIN CATCH
PRINT ‘And nor does this print’
END CATCH
go
EXEC inner_sp
What is a way to catch this error?
It does not catch compilation errors that occur in the same scope.
The output is:
This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4 Invalid object name ‘NoSuchTable’
To catch the error, you can use an outer scope:
CREATE PROC outer_sp
AS
BEGIN TRY
EXEC inner_sp
END TRY
BEGIN CATCH
PRINT ‘The error message is: ‘ + error_message()
END CATCH
go
EXEC outer_sp
BEGIN TRANSACTION marks the starting point of an explicit, user-defined transaction. How long does the transaction last?
Transactions last until a COMMIT statement is issued, a ROLLBACK is manually issued, or the connection is broken and the system issues a ROLLBACK
Transaction syntax with error-handling
BEGIN TRY
BEGIN TRAN
….code….
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
;THROW
END CATCH;
