Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.