SQL Query Timing Issue?

Question

I have a stored procedure that uses a lot of dynamic SQL to process some address data for clients.

The first step is that it looks for addresses that are blank and puts them into a table with a [reason] code.

The next step looks for addresses that are just missing the address line field, and I want to exclude anything found in the first step. However, it keeps returning everything from the first step.

If I run the query by itself afterwards it returns the correct results which makes me think that the first insert may not be complete before the second query is run. If that is the case, what can I do to ensure one query is complete before the next goes?

Here is my code:

/***************************** BLANK ADDRESS *****************************/
/*                                                                       */
/*  Finds records that are missing Address Line 1, City and State        */
/*                                                                       */
/*************************************************************************/

-- Address 1
IF (@add1 IS NOT NULL) AND (@city IS NOT NULL) AND (@state IS NOT NULL)
BEGIN
    SET @sql = 'INSERT INTO ' + @db + '.[address_issues] ' +
        'SELECT ''Blank Address 1''' + @sql_output +
        ' FROM ' + @db + '.' + @table + ' WHERE '
            IF (@add1 IS NOT NULL)              SET @sql = @sql + '(' + @add1 + ' = '''' OR ' + @add1 + ' IS NULL) AND'
            IF (@add2 IS NOT NULL)              SET @sql = @sql + '(' + @add2 + ' = '''' OR ' + @add2 + ' IS NULL) AND'
            IF (@city IS NOT NULL)              SET @sql = @sql + '(' + @city + ' = '''' OR ' + @city + ' IS NULL) AND'
            IF (@state IS NOT NULL)             SET @sql = @sql + '(' + @state + ' = '''' OR ' + @state + ' IS NULL)'

    -- Inserts query into table for output
    PRINT 'Blank Address 1 SQL: ' + @sql
    EXEC sp_executesql @sql
    SET @sql = ''
END

/***************************** EMPTY ADDRESS FIELD *****************************/
/*                                                                             */
/*  Identifies records that are missing the Add1 field, but are not already    */
/*      included in the Blank Addresses above                                  */
/*                                                                             */
/*******************************************************************************/

-- Address 1, Line 1
IF (@add1 IS NOT NULL) AND (@city IS NOT NULL) AND (@state IS NOT NULL)
BEGIN
    SET @sql = 'INSERT INTO ' + @db + '.[address_issues] ' +
        'SELECT ''Address 1: Empty Address Line 1''' + @sql_output +
        ' FROM ' + @db + '.' + @table + ' WHERE ('
            IF (@add1 IS NOT NULL)              SET @sql = @sql + '(' + @add1 + ' = '''' OR ' + @add1 + ' IS NULL) AND ( '
            IF (@add2 IS NOT NULL)              SET @sql = @sql + '(' + @add2 + ' <> '''' OR ' + @add2 + ' IS NOT NULL) OR '
            IF (@city IS NOT NULL)              SET @sql = @sql + '(' + @city + ' <> '''' OR ' + @city + ' IS NOT NULL) OR '
            IF (@state IS NOT NULL)             SET @sql = @sql + '(' + @state + ' <> '''' OR ' + @state + ' IS NOT NULL) )'
    SET @sql = @sql +
        ') AND (' +
            @id + ' NOT IN (SELECT [Data ID] FROM [address_issues] WHERE [Reason] = ''Blank Address 1'' AND [Data ID] = ' + @id
            IF (@system IS NOT NULL)            SET @sql = @sql + ' AND [System] = ' + @system
            IF (@id2 IS NOT NULL)               SET @sql = @sql + ' AND [Data ID2] = ' + @id2
    SET @sql = @sql + ') )'

    -- Inserts query into table for output
    PRINT 'Address 1: Empty Address Line 1 SQL: ' + @sql
    EXEC sp_executesql @sql
    SET @sql = ''
END

The code after is several more similar checks that have similar issues. I then print out the address_issues table.

Here is the SQL for the two queries since the dynamic SQL may not make as much sense.

Blank address #1 SQL:

INSERT INTO [SW].[dbo].[address_issues] 
    SELECT 'Blank Address 1', [Company], [Supplier_No], [Name], [Address], [City], [State], [Zip], [Country] 
    FROM [SW].[dbo].[vendors_plex_raw] 
    WHERE ([Address] = '' OR [Address] IS NULL) 
      AND ([City] = '' OR [City] IS NULL) 
      AND ([State] = '' OR [State] IS NULL)

Address 1: empty address line 1 SQL:

INSERT INTO [SW].[dbo].[address_issues] 
    SELECT 'Address 1: Empty Address Line 1', [Company], [Supplier_No], [Name], [Address], [City], [State], [Zip], [Country] 
    FROM [SW].[dbo].[vendors_plex_raw] r
    WHERE (([Address] = '' OR [Address] IS NULL) AND 
            (([City] <> '' OR [City] IS NOT NULL) OR 
             ([State] <> '' OR [State] IS NOT NULL))) AND 
          ([Supplier_No] NOT IN (SELECT [Data ID] 
                                 FROM [address_issues] 
                                 WHERE [Reason] = 'Blank Address 1' 
                                   AND [Data ID] = [Supplier_No] 
                                   AND [System] = [Company]))

Sample Data and Query Results - as shown below

Company Supplier_No Name    Address City    State   Zip Country
CO1 1252725 Sample Company 1    29833 Narwall Ave   Chicago IL  60673-1298  USA
CO1 1252726 Sample Company 2        Cleveland   OH  44114   USA 
CO1 1252727 Sample Company 3        Cleveland   OH  44118   USA
CO1 1252728 Sample Company 4                    USA
CO1 1252729 Sample Company 5                    
CO2 1252730 Sample Company 6    375 Saxon Pl    Saxonburg   PA  16056   USA
CO2 1252731 Sample Company 7        Austin  TX  78745   USA
CO2 1252732 Sample Company 8        Brooklyn    NY  11210   USA
CO2 1252798 Sample Company 9                    USA
CO2 1252797 Sample Company 10                   
                            
                            
Expected Query 1 Results  (Works as Expected)                           
CO1 1252728 Sample Company 4                    USA
CO1 1252729 Sample Company 5                    
CO2 1252798 Sample Company 9                    USA
CO2 1252797 Sample Company 10                   
                            
                            
Expected Query 2 Results                            
CO1 1252726 Sample Company 2        Cleveland   OH  44114   USA
CO1 1252727 Sample Company 3        Cleveland   OH  44118   USA
CO2 1252731 Sample Company 7        Austin  TX  78745   USA
CO2 1252732 Sample Company 8        Brooklyn    NY  11210   USA

                            
Query 2 Current/Actual Results                          
CO1 1252726 Sample Company 2        Cleveland   OH  44114   USA
CO1 1252727 Sample Company 3        Cleveland   OH  44118   USA
CO1 1252728 Sample Company 4                    USA
CO1 1252729 Sample Company 5                    
CO2 1252731 Sample Company 7        Austin  TX  78745   USA
CO2 1252732 Sample Company 8        Brooklyn    NY  11210   USA
CO2 1252798 Sample Company 9                    USA
CO2 1252797 Sample Company 10

SSMS v18.5
SQL Server 2019 (v15.0.4083.2)

Please don't use images for data - use formatted text - and if you want to make it easy for people to assist, provide your sample data as DDL+DML. — Dale K, Apr 05 '21 at 20:34
@DaleK Tried to add formatted text but the format got lost. I'll see about DDL and DML — Dizzy49, Apr 05 '21 at 20:45
Seems OK to me? Just format as code... (which I think you have done). — Dale K, Apr 05 '21 at 20:46
A couple things to check. I noticed you didn't specify SW.dbo. for the table address_issues in your "not in" nested select. In both of your if statements, your checks are checking for if not null on all 3 columns even though you mention your looking for empty addresses. Lastly, we didn't get the table definitions for address_issues, but I'd double check your column orders on your inserts since you didn't explicitly specify the column names your inserting into. — kaladin_storm, Apr 05 '21 at 21:09
@kaladin_storm (facepalm) That was it, I needed to specify the database! THANK YOU! — Dizzy49, Apr 05 '21 at 21:17
@kaladin_storm If you submit an answer I'll mark it as the solution since you resolved it first. — Dizzy49, Apr 06 '21 at 01:23

score 1 · Answer 1 · answered Apr 05 '21 at 21:45

First, I would spell out the column names.

INSERT INTO table
(Col1, Col2, ...)
SELECT ...

Otherwise, someone adding a column that you don't even care about will break your code.

Second, you have INSERT INTO [SW].[dbo].[address_issues] and then FROM [address_issues]

If you are running this from another database other than SW, there will be a mismatch.

score 1 · Accepted Answer · answered Apr 06 '21 at 03:44

1

The SW.dbo. is missing from the address_issues table in the nested select

answered Apr 06 '21 at 03:44

kaladin_storm

352
1
7

SQL Query Timing Issue?

2 Answers2