Post reply

Check to see if columns are NOT NULL in a record

  • June 25, 2015 at 8:21 am

    #305395

    I am looking for the best way to check to see if any columns are still NULL in a record. I have a form that gets filled out by users and the values entered into TableA. There are 6 columns in the table, 5 are responses and column6 indicates if the record is complete. So I want a way to see if all of the first 5 columns are NOT NULL and, if so, mark column6 with a 1.

    I am thinking this would be a good thing for a trigger on INSERT or UPDATE to check to see if the first 5 columns are filled in and then mark the record as complete.

    Thanks for suggestions.

  • June 25, 2015 at 8:45 am

    #1808095

    I would think a simple UPDATE statement would do the trick.

    UPDATE table_name

    SET col6 = 1

    WHERE col1 IS NOT NULL

    AND col2 IS NOT NULL

    AND col3 IS NOT NULL

    AND col4 IS NOT NULL

    AND col5 IS NOT NULL;

    You could also populate the column when you write the row to the table so you wouldn't have to set it later. That way, the data would always be in the state you need.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 25, 2015 at 8:52 am

    #1808102

    Yes, or better still, use a computed column. Then it's always up to date and it doesn't take up any space in the database.

    John

  • June 25, 2015 at 8:54 am

    #1808104

    Well that seems pretty simple. Thanks.

    I can't really set the value when creating the record. There are actually 23 entries on the form, not just 5. I kept the number low just for simplicity. The program is designed so that the user can fill out some of the fields and save them (creating the initial INSERT) but then come back later to finish the responses. So They may work on it a few times, saving more responses each time. That is why I want to do it this way, so that when they have finally completed ALL fields, then it will be marked complete. Until then, it will be incomplete.

    Thanks for the help!

  • June 25, 2015 at 9:33 am

    #1808127

    -- Have you tried a computed column?

    DROP TABLE #NameAndAddress

    CREATE TABLE #NameAndAddress (

    NameAndAddressID INT NOT NULL identity(1,1),

    Firstname VARCHAR(20),

    Lastname VARCHAR(20),

    Address1 VARCHAR(50),

    Address2 VARCHAR(50),

    Address3 VARCHAR(50),

    Address4 VARCHAR(50),

    RowIsComplete AS CASE

    WHEN Firstname IS NULL

    OR Lastname IS NULL

    OR Address1 IS NULL

    OR Address2 IS NULL

    OR Address3 IS NULL

    OR Address4 IS NULL THEN 0 ELSE 1 END

    )

    INSERT INTO #NameAndAddress (Firstname, Lastname, Address1, Address2, Address3, Address4)

    SELECT 'Firstname', 'Lastname', 'Address1', 'Address2', 'Address3', 'Address4' UNION ALL

    SELECT NULL, 'Lastname', 'Address1', 'Address2', 'Address3', 'Address4' UNION ALL

    SELECT 'Firstname', NULL, 'Address1', 'Address2', 'Address3', 'Address4' UNION ALL

    SELECT 'Firstname', 'Lastname', NULL, 'Address2', 'Address3', 'Address4' UNION ALL

    SELECT 'Firstname', 'Lastname', 'Address1', 'Address2', 'Address3', 'Address4'

    SELECT *

    FROM #NameAndAddress

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 25, 2015 at 9:41 am

    #1808128

    No, I have not... but NICE! I like it.

    Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply