Session 4 - Combine datasets, Stored Procs, Error Checking

Question 1

What are the criteria for using UNION or UNION ALL?

Answer 1

Same Number of Columns

Same or compatible datatypes

Question 2

What’s the difference between union and union all?

Answer 2

Union returns only unique values between two tables and union all returns all values between two tables

Question 3

Where do you put the ORDER BY in a UNION, UNION ALL, INTERSECT, EXCEPT?

Answer 3

AT THE END OF THE STATEMENT

Question 4

What does UNION show you?

Answer 4

It shows the unique rows in two tables

Question 5

What does UNION ALL show you?

Answer 5

It shows all rows in two tables

Question 6

What does INTERSECT show you?

Answer 6

It shows all rows that are exactly the same in both tables.

Question 7

What does EXCEPT show you?

Answer 7

It shows all rows that DO NOT exist in the second table.

Question 8

Where are the column names set in a UNION, INTERSECT, or EXCEPT statement?

Answer 8

In the first select statement

Question 9

What are the two syntax for case?

Answer 9

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

Question 10

What option is not required for a case statement?

Answer 10

ELSE

Question 11

Will selecting ISNULL into a table create a nullable column?

Answer 11

No, it will create the column as NOT NULL

Question 12

What is the syntax of ISNULL?

Answer 12

SELECT ISNULL(@myVar, 'No Option')
It will check to see if variable is null and if so, return the second parameter

Question 13

What is the syntax of COALESCE?

Answer 13

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.

Question 14

What is the datatype gotcha with coalesce?

Answer 14

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

Question 15

Can you only return NULL from coalesce?

Answer 15

No, you are required to return a datatype other than null

Question 16

What is the syntax of a try statement?

Answer 16

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

Question 17

What is ERROR_SEVERITY()?

Answer 17

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

Question 18

What is ERROR_LINE()?

Answer 18

The line where the error occurred. In a stored procedure, the line numbers start directly after GO

Question 19

What is ERROR_PROCEDURE()?

Answer 19

The procedure name where the error occurred.

Question 20

What is ERROR_NUMBER()?

Answer 20

The recorded error number that microsoft gives to the error. This can be searched after the fact.

Question 21

What is the syntax of THROW

Answer 21

You can use it as is to throw the original error, or the parameters are ERROR_NUMBER, ERROR_MESSAGE, ERROR_STATE (OR 1)

Question 22

What is the syntax of RAISERROR

Answer 22

RAISERROR(‘ERROR_MESSAGE’, ERROR_SEVERITY, STATE)

Question 23

In a MERGE statement, what are the two tables named?

Answer 23

TARGET (Original table)

SOURCE (New Table)

Question 24

What are the possibilities of MERGE statement?

Answer 24

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]

Question 25

What is the full syntax of a MERGE statement?

Answer 25

MERGE INTO [TARGET TABLE] AS ALIAS1
USING [SOURCE TABLE] AS ALIAS2
ON ALIAS1.COL1 = ALIAS2.COL1
WHEN MATCHED THEN 
    UPDATE SET COL1 = 'VALUE'
WHEN NOT MATCHED BY TARGET THEN
	INSERT (COL1, COL2, COL3)
	VALUES (ALIAS2.COL1, ALIAS2.COL2, ALIAS2.COL3)
[OUTPUT inserted.*, deleted.*]; -- To be able to show columns inserted and deleted
ALWAYS HAVE TO END IT WITH A SEMICOLON

Question 26

What is the syntax for creating a stored procedure?

Answer 26

CREATE PROCEDURE (
     @VARIABLE DATATYPE
) AS 
BEGIN
	SET NOCOUNT ON
	QUERY
END

Question 27

What is the syntax for a WHILE statement?

Answer 27

WHILE @var1 < @var2
BEGIN
    STATEMENT
    SET @var1 = @var1 + 1
END

Question 28

How do you get a return value from a stored procedure?

Answer 28

Add a variable with datatype and OUTPUT in the parameters section:

CREATE PROC ProcName (@var1 int, @OutputVar int OUTPUT) as …

Then when you call the SP, you have to put a variable and output in as well:
DECLARE @OutVar
EXEC ProcName 123, @OutVar OUTPUT

Question 29

How can you tell if a procedure has ended in success or failure?

Answer 29

use RETURN 0 for success and RETURN 1 for failure:

CREATE PROCEDURE DBO.MYPROCEDURE (@ABNUM INT, @OUTVAR INT OUTPUT) AS
BEGIN 
	IF EXISTS(SELECT * FROM JDE_PRODUCTION.PRODDTA.F0101 WHERE ABAN8 = @ABNUM)
	BEGIN
		SELECT * FROM JDE_PRODUCTION.PRODDTA.F0101 WHERE ABAN8 = @ABNUM
		SET @OUTVAR = @@ROWCOUNT
		RETURN 0
	END
	ELSE
		SET @OUTVAR = 0
		RETURN 1
END

DECLARE @OUTPT INT
EXEC @OUTPUT = DBO.MYPROCEDURE 999998
SELECT @OUTPT

Question 30

What are the functions for seeing the specifics of an error?

Answer 30

ERROR_MESSAGE()
ERROR_LINE()
ERROR_NUMBER()
ERROR_PROCEDURE()
ERROR_SEVERITY()
ERROR_STATE()