dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23

Thread: SQL Server UNION

  1. #16
    Join Date
    Jan 2013
    Posts
    72

    Re: SQL Server UNION

    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:
    Code:
    	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
    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
    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 :
    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') '
    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.

  2. #17
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,808

    Re: SQL Server UNION

    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.

  3. #18
    Join Date
    Jan 2013
    Posts
    72

    Re: SQL Server UNION

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

    Thank you

  4. #19
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,808

    Re: SQL Server UNION

    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.

  5. #20
    Join Date
    Jan 2013
    Posts
    72

    Re: SQL Server UNION

    I have this in stored Procedure:

    Code:
    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
    Code:
    				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:

    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

  6. #21
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,808

    Re: SQL Server UNION

    Declare the temporary table inside the dynamic sql string.

  7. #22
    Join Date
    Jan 2013
    Posts
    72

    Re: SQL Server UNION

    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.

  8. #23
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,808

    Re: SQL Server UNION

    Quote Originally Posted by chapran View Post
    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).

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)