Throwing a custom error in a stored procedure

Question

I'm wanting to throw an error when reference keys are missing, however instead of failing through a integrity check, I want to list out the missing keys. I've created the below which works. However I'm hoping there is a way to optimise it and reduce the number of lines of code.

DECLARE @NonRefKeys INT
SELECT @NonRefKeys = SUM(1)
FROM staging.Sale sa
WHERE NOT EXISTS (
 SELECT cu.Customer_Shipping_ID
 FROM staging.Customer cu
 WHERE LTRIM(RTRIM(sa.Customer_Shipping_ID)) = LTRIM(RTRIM(cu.Customer_Shipping_ID)))
IF @NonRefKeys IS NOT NULL
BEGIN
IF OBJECT_ID('tempdb..#Missing_Ref') IS NOT NULL
DROP TABLE #Missing_Ref;
SELECT sa.Customer_Shipping_ID AS ID
INTO #Missing_Ref
FROM staging.Sale sa
WHERE NOT EXISTS (
 SELECT cu.Customer_Shipping_ID
 FROM staging.Customer cu
 WHERE LTRIM(RTRIM(sa.Customer_Shipping_ID)) = LTRIM(RTRIM(cu.Customer_Shipping_ID)))
DECLARE @Current_ID VARCHAR(50);
DECLARE @Missing_ID VARCHAR(MAX) = '';
DECLARE @Output_Error VARCHAR(MAX);
DECLARE id_cursor CURSOR FOR
SELECT ID
FROM #Missing_Ref;
OPEN id_cursor
FETCH NEXT FROM id_cursor INTO @Current_ID

Answer 1

I'm not sure I completely understand your logic, but since this is a stored procedure the temp table isn't visible outside it so you don't need to check if it exists (unless you have other code in the same procedure that might have created it already).

And the most obvious way to reduce the number of lines is not to count the number of IDs and then get them, but to just get them and use @@ROWCOUNT to see how many you got:

-- get missing IDs
SELECT sa.Customer_Shipping_ID AS ID
INTO #Missing_Ref
FROM staging.Sale sa
WHERE NOT EXISTS (
 SELECT cu.Customer_Shipping_ID
 FROM staging.Customer cu
 WHERE LTRIM(RTRIM(sa.Customer_Shipping_ID)) = LTRIM(RTRIM(cu.Customer_Shipping_ID)))
-- only continue if we got at least 1 missing ID
if @@rowcount > 0
begin
 DECLARE @Current_ID VARCHAR(50);
 DECLARE @Missing_ID VARCHAR(MAX) = '';
 DECLARE @Output_Error VARCHAR(MAX);
 DECLARE id_cursor CURSOR FOR
 SELECT ID
 FROM #Missing_Ref;
 -- rest of cursor here (presumably)
end

BTW, the title of your question seems to have nothing to do with the actual code, which is concerned with finding a set of rows that may or may not exist.

Answer 2

Regarding your question, I would suggest that you use RAISERROR() for this.

See this example:

IF(@id IS NULL) BEGIN
 RAISERROR('@id is null, which shouldn't be',16,1);
END

The first part is the error message which will be thrown as msg. 50.000 to the user/application. The 16 is the severity. The severity can be from 1 to 21.>18 can just be thrown by the SA with the WITH LOG option. Severity under 16 won't be read and won't cause an abort. They are just warnings. Severity 16 will cause an abort of the current (sub)procedure. Severity 18 will cause an abort of the whole procedure stack. Unless if they are catches in a try catch.