Sorry for the delay. I had to finish and test to be sure that it works properly. The solution for AND I created looks like this:
For OR I used UNIONCode:If Not(@JobNumber Is Null)
BEGIN
SET @sql = N'
SELECT JobID
FROM dbo.Sopa
WHERE (CompanyID = ' + CAST(@CompanyID AS varchar(5)) + N') AND (RowDeleted = 0 OR RowDeleted IS NULL) AND
(JobNumber LIKE ' + char(39) + N'%' + @JobNumber + N'%' + char(39) + N') '
END
If Not(@JobName Is Null)
BEGIN
IF @sql IS NULL
SET @sql = N'
SELECT JobID
FROM dbo.Sopa
WHERE (CompanyID = ' + CAST(@CompanyID AS varchar(5)) + N') AND
(RowDeleted = 0 OR RowDeleted IS NULL) AND
(JobName LIKE ' + char(39) + N'%' + @JobName + N'%' + char(39) + N') '
ELSE
SET @sql = @sql + N' AND
(JobName LIKE ' + char(39) + N'%' + @JobName + N'%' + char(39) + N') '
END
If Not(@JobLocation Is Null)
BEGIN
IF @sql IS NULL
SET @sql = N'
SELECT JobID
FROM dbo.Sopa
WHERE (CompanyID = ' + CAST(@CompanyID AS varchar(5)) + N') AND (RowDeleted = 0 OR RowDeleted IS NULL) AND
(JobLocation LIKE ' + char(39) + N'%' + @JobLocation + N'%' + char(39) + N') '
ELSE
SET @sql = @sql + N' AND (JobLocation LIKE ' + char(39) + N'%' + @JobLocation + N'%' + char(39) + N') '
END
and it is not good for AND in case of different set of tables.Code:If Not(@JobNumber Is Null)
BEGIN
SET @sql = N'
SELECT JobID
FROM dbo.Sopa
WHERE (CompanyID = ' + CAST(@CompanyID AS varchar(5)) + N') AND (JobNumber LIKE ' + char(39) + N'%' + @JobNumber + N'%' + char(39) + N') AND
(RowDeleted = 0 OR RowDeleted IS NULL) '
END
If Not(@JobName Is Null)
BEGIN
IF @sql IS NULL
SET @sql = N''
ELSE
SET @sql = @sql + N' UNION '
SET @sql = @sql + N'
SELECT JobID
FROM dbo.Sopa
WHERE (CompanyID = ' + CAST(@CompanyID AS varchar(5)) + N') AND (JobName LIKE ' + char(39) + N'%' + @JobName + N'%' + char(39) + N') AND
(RowDeleted = 0 OR RowDeleted IS NULL) '
END
If Not(@JobLocation Is Null)
BEGIN
IF @sql IS NULL
SET @sql = N''
ELSE
SET @sql = @sql + N' UNION '
SET @sql = @sql + N'
SELECT JobID
FROM dbo.Sopa
WHERE (CompanyID = ' + CAST(@CompanyID AS varchar(5)) + N') AND (JobLocation LIKE ' + char(39) + N'%' + @JobLocation + N'%' + char(39) + N') AND
(RowDeleted = 0 OR RowDeleted IS NULL) '
END
If Not(@BidDate Is Null)
BEGIN
IF @sql IS NULL
SET @sql = N''
ELSE
SET @sql = @sql + N' UNION '
SET @sql = @sql + N'
SELECT JobID
FROM dbo.Sopa
WHERE (CompanyID = ' + CAST(@CompanyID AS varchar(5)) + N') AND
(dbo.fnFormatDate(dbo.Sopa.BidDate, ' + char(39) + N'MM/DD/YYYY' + char(39) + N') = dbo.fnFormatDate(' + char(39) + @BidDate + char(39) + ', ' + char(39) + N'MM/DD/YYYY' + char(39) + N')) AND
(RowDeleted = 0 OR RowDeleted IS NULL) '
END
Unfortunately I cannot add blocks of code which have selection from other than Sopa table.
For instance this block (modified) I cannot add to the selection :
I'm thinking about using temporary tables to store intermidiate result there and then combine somehow with the results taken from selections from different joins.Code:If Not(@EstimatorID Is Null)
BEGIN
IF @sql IS NULL
SET @sql = N''
ELSE
SET @sql = @sql + N' UNION '
SET @sql = N'
SELECT DISTINCT dbo.Sopa.JobID
FROM dbo.Sopa INNER JOIN dbo.SopaEstimator ON dbo.Sopa.JobID = dbo.SopaEstimator.JobID
WHERE (Sopa.CompanyID = ' + CAST(@CompanyID AS varchar(5)) + N') AND (dbo.SopaEstimator.RowDeleted IS NULL OR dbo.SopaEstimator.RowDeleted = 0) AND (dbo.SopaEstimator.Assigned = 1) AND
(dbo.Sopa.RowDeleted IS NULL OR dbo.Sopa.RowDeleted = 0) AND (dbo.SopaEstimator.SalesmanID = ' + CAST(@EstimatorID AS varchar(5)) + N') '
Maybe there is some better scenario?
Thank you.