I have a straight forward SQL query that I am working with and trying to figure out the best way to approach the
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.
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', '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', '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.