TSQL Where clause based on temp table data

Asked at 2017-01-11 16:03:01Z
  • 5 Subscribers
  • 114 Views
0

I have a straight forward SQL query that I am working with and trying to figure out the best way to approach the where clause.

Essentially, there are two temp tables created and if there is data in the XML string passed to the stored procedure, those tables are populated.

My where clause needs to check these temp tables for data, and if there is no data, it ignores them like they are not there and fetches all data.

    -- Create temp tables to hold our XML filter criteria
    DECLARE @users AS TABLE (QID VARCHAR(10))
    DECLARE @dls   AS TABLE (dlName VARCHAR(50))

    -- Insert our XML filters
    IF @xml.exist('/root/data/users') > 0
    BEGIN
        INSERT INTO @users( QID )
        SELECT ParamValues.x1.value('QID[1]', 'varchar(10)')
        FROM   @xml.nodes('/root/data/users/user') AS ParamValues(x1)
    END
    IF @xml.exist('/root/data/dls') > 0
    BEGIN
        INSERT INTO @dls( dlName )
        SELECT ParamValues.x1.value('dlName[1]', 'varchar(50)')
        FROM   @xml.nodes('/root/data/dld/dl') AS ParamValues(x1)
    END

    -- Fetch our document details based on the XML provided
    SELECT d.documentID ,
           d.sopID ,
           d.documentName ,
           d.folderLocation ,
           d.userGroup ,
           d.notes 
    FROM dbo.Documents AS d
    LEFT JOIN dbo.DocumentContacts AS dc
    ON dc.documentID = d.documentID
    LEFT JOIN dbo.DocumentContactsDLs AS dl
    ON dl.documentID = d.documentID
    -- How can I make these two logic checks work only if there is data, otherwise, include everything.
    WHERE dc.QID IN (SELECT QID FROM @users)
    AND dl.DL IN (SELECT dlName FROM @dls)

    FOR    XML PATH ('data'), ELEMENTS, TYPE, ROOT('root');

In the query above, I am trying to used the data in the temp tables only if there is data in them, otherwise, it needs to act like that where statement isn't there for that specific value and include records regardless.

Example: If only @users had data, it would ignore AND dl.DL IN (SELECT dlName FROM @dls) and get everything, regardless of what was in the DL column on those joined records.


2 answers in total

0
Prdp Posted at 2017-01-11 16:05:01Z

Use NOT EXISTS to check the existence of any record in variable table. Here is one way

WHERE  ( dc.QID IN (SELECT QID FROM @users)
          OR NOT EXISTS (SELECT 1 FROM @users) )
       AND ( dl.DL IN (SELECT dlName FROM @dls)
              OR NOT EXISTS (SELECT 1 FROM @dls) ) 
0
Gopakumar N.Kurup Posted at 2017-01-11 16:38:49Z

Try this. But please note that I did not get a chance to test it properly and I believe that you want to check the values in @users first and if there is no record existing in that table, then you want to check with the entries in @dls. Also if there are no entries in both of these tables, then you want to skip both the tables.

DECLARE @fl bit = 0
SELECT @fl = CASE WHEN EXISTS (SELECT 1 FROM @users) THEN 
 1
WHEN EXISTS (SELECT 1 FROM @dls) THEN  
2
ELSE 
0
END

WHERE  (    (dc.QID IN (SELECT QID FROM @users) AND @fl = 1)
        OR 
            (dl.DL IN (SELECT dlName FROM @dls) AND @fl = 2)
        OR  (1=1 AND @fl = 0)
        )

Answer this questsion