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.