SQL Server UNION

[chapran]

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:

	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

For OR I used UNION

	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

and it is not good for AND in case of different set of tables.
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 :

	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') '

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.
Maybe there is some better scenario?
Thank you.

[Arjay]

What version of Sql server are you using? I ask because newer versions support things like common table expreession and table variables which you might find useful.

[chapran]

2008 R2.
As I know table variables are supported. I know nothing about table expression.

Thank you

[Arjay]

I don't think CTEs are supported in 2008R2 (you can look it up for sure). Table variables are cool because you don't need to clean them up (as they just go out of scope). However, unlike a temp table you can't create explicit indexes to the columns. So if you temp table is storing large data sets where you need an index to help performance when joing to other tables, the temp table will work better than the table variable. But, we're talking like over 10,000 rows or more. Small data sets work fine with table variables.

[chapran]

I have this in stored Procedure:

DECLARE @1_Main TABLE (JobID int)

--I create dynamic sql:
		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

It is more complex, but the idea is just ADD additional parameters in WHERE.
After I created @sql I would like to insert data from that @sql into temp table.
If I try

				SET @sql = N'INSERT INTO ' + @1_Main + N'(JobID) ' + @sql

it says 'must declare the scalar variable @1_Main right in the design.
I get the same message when I execute this procedure with this code:

				SET @sql = N'INSERT INTO @1_Main(JobID) ' + @sql

What is the correct syntax for insert into variable table using dynamic sql?

I need to store intermidiate results in the temporary place in order to get selection from this and some other tables.

Thank you

[Arjay]

Declare the temporary table inside the dynamic sql string.

[chapran]

Thank you. It looks to complex to work with variable table in my case - a lot of parameters and many tables are used in selections and filters. Variable tables are visible inside of dynamic string, but they are not visible outside. I decided to try temp tables. Did not finish testing yet, but it looks like simpler.

[Arjay]

Thank you. It looks to complex to work with variable table in my case - a lot of parameters and many tables are used in selections and filters. Variable tables are visible inside of dynamic string, but they are not visible outside. I decided to try temp tables. Did not finish testing yet, but it looks like simpler.

How you declare a table depends on the table lifetime (scope) requirements. Table scope changes with a table variable (@mytable), a temp table (#mytable), or a global table (##mytable).

In a way, it's similar in programming as declaring variables on the stack (like table variables that you don't have to clean up) vs. newing up variables on the heap (like temp or global tables that you have to explicitly clean up).