ExecuteScalar:
MSDN:
Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
You have no SELECT inside your SP so not rows and columns, so ExecuteScalar returns null - and that is a null reference error...
You probably have to replace this line:
Return @CheckType
To this:
SELECT @CheckType