David David - 6 months ago 11
SQL Question

Inserting strings into an SQL table depending on what results a query returns

I have a handful of queries that I'm using to test whether or not database results from tests that I run don't have anything obviously broken and buggy in them.

One of the queries has this basic form:

SELECT *
FROM Table
WHERE Column = ''


Its checking to make sure that one particular field is not blank. I want to keep track of these tests being run and when they pass or fail. Is there a way that I can write a query so that if that query brings back any results then it writes a string to another table that says something like "Test Passed"?

So, a pseudo version might look something like this:

IF (
SELECT *
FROM Table1
WHERE Table1.Column1 = ''
) = 0
INSERT INTO Table2 (FileName, Date, Result)
VALUES ('File1', 'GetDate()', 'Test Passed')
ELSE
INSERT INTO Table2 (FileName, Date, Result)
VALUES ('File1', 'GetDate()', 'Test Failed')


And the table might look like this:

| FileName | Date | Result |
|:--------:|:--------:|:-----------:|
| File1 | 12-25-16 | Test Passed |
| File2 | 12-25-16 | Test Failed |
| File3 | 12-25-16 | Test Passed |
| File4 | 12-25-16 | Test Passed |

Answer

You almost have it in your code. Just change the SELECT * to SELECT COUNT(*) and you've got it . I would set the status and then do the insert.

DECLARE @testStatus NVARCHAR(MAX);

IF (
    SELECT COUNT(*)
    FROM Table1
    WHERE Table1.Column1 = ''
) = 0
    SET @testStatus = 'Test Passed'
ELSE
    SET @testStatus = 'Test Failed'

INSERT INTO Table2 (FileName, Date, Result)
VALUES ('File1', GetDate(), @testStatus)