Session 4 - Combine datasets, Stored Procs, Error Checking Flashcards
What are the criteria for using UNION or UNION ALL?
Same Number of Columns
Same or compatible datatypes
What’s the difference between union and union all?
Union returns only unique values between two tables and union all returns all values between two tables
Where do you put the ORDER BY in a UNION, UNION ALL, INTERSECT, EXCEPT?
AT THE END OF THE STATEMENT
What does UNION show you?
It shows the unique rows in two tables
What does UNION ALL show you?
It shows all rows in two tables
What does INTERSECT show you?
It shows all rows that are exactly the same in both tables.
What does EXCEPT show you?
It shows all rows that DO NOT exist in the second table.
Where are the column names set in a UNION, INTERSECT, or EXCEPT statement?
In the first select statement
What are the two syntax for case?
SELECT CASE WHEN @myVar = ‘val’ THEN ‘option’
WHEN @myVar = ‘otherVal’ THEN ‘OtherOption’
ELSE ‘NoOptionsFound’
END
SELECT CASE @myVar WHEN ‘val’ THEN ‘option’
WHEN ‘otherVal’ THEN ‘OtherOption’
ELSE ‘NoOptionsFound’
END
What option is not required for a case statement?
ELSE
Will selecting ISNULL into a table create a nullable column?
No, it will create the column as NOT NULL
What is the syntax of ISNULL?
SELECT ISNULL(@myVar, 'No Option') It will check to see if variable is null and if so, return the second parameter
What is the syntax of COALESCE?
select coalesce(@myVar1, @myVar2, @myVar3, ..., 'No Option') It will go through each parameter until it comes to a non null which it will then return.
What is the datatype gotcha with coalesce?
It will go through the results of each parameter and return the highest order data type or error if it cannot return. Make sure that all parameters return compatible data types
Can you only return NULL from coalesce?
No, you are required to return a datatype other than null
What is the syntax of a try statement?
BEGIN TRY STATEMENT RETURN 0 --To return that it completed successfully END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage RETURN 1 --To return that it's an error END CATCH
What is ERROR_SEVERITY()?
The severity of the error that is caught:
0-10 for information (Can ignore)
16 is default (Logged in SQL Server log and Windows app log)
20-25 SEVERE stops database critical
What is ERROR_LINE()?
The line where the error occurred. In a stored procedure, the line numbers start directly after GO
What is ERROR_PROCEDURE()?
The procedure name where the error occurred.
What is ERROR_NUMBER()?
The recorded error number that microsoft gives to the error. This can be searched after the fact.
What is the syntax of THROW
You can use it as is to throw the original error, or the parameters are ERROR_NUMBER, ERROR_MESSAGE, ERROR_STATE (OR 1)
What is the syntax of RAISERROR
RAISERROR(‘ERROR_MESSAGE’, ERROR_SEVERITY, STATE)
In a MERGE statement, what are the two tables named?
TARGET (Original table)
SOURCE (New Table)
What are the possibilities of MERGE statement?
When MATCHED [AND SOMETHING ELSE] then [UPDATE, DELETE]
WHEN NOT MATCHED [BY TARGET] THEN [INSERT]
WHEN NOT MATCHED BY SOURCE [AND SOMETHING ELSE] THEN [UPDATE, DELETE]