CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2013
    Posts
    90

    SQL Server UNION

    I have a stored procedure in SQL Server for search:
    Code:
    ALTER PROCEDURE [dbo].[SopaSearch_Search] 
    @JobNumber varchar(9)=Null,
    @JobName varchar(50)=Null,
    @JobLocation varchar(50)=Null,
    @BidDate varchar(20)=Null,
    @DodgeNumber varchar(15)=Null,
    @TakeOffPersonID int=Null,--Not In Use
    @EstimatorID int=Null,
    @StatusID int=Null,
    @PriorityID int=Null,
    @EngineerID int=Null,
    @LowBidderID int=Null,
    @ProjectTypeID int=Null,
    @BidderID int=Null,
    @ProjectManagerID int=Null,
    @CompanyID int,
    @DontDisplayArchivedData bit,
    @Notes varchar(4000) = Null,
    @WonNoOrder int = NULL,
    @TitleBlockName varchar(150) = Null
    
    AS
    SET NOCOUNT ON
    
    If @DontDisplayArchivedData=0
    	BEGIN
    	If Not(@JobNumber Is Null)
    		BEGIN
    			SELECT     JobID
    			FROM         dbo.Sopa
    			WHERE     (CompanyID = @CompanyID) AND (JobNumber LIKE '%' + @JobNumber + '%') AND 
    				(RowDeleted = 0 OR RowDeleted IS NULL)
    			ORDER BY JobID
    		END
    	Else If Not(@JobName Is Null)
    		BEGIN
    			SELECT     JobID
    			FROM         dbo.Sopa
    			WHERE     (CompanyID = @CompanyID) AND (JobName LIKE '%' + @JobName + '%') AND (RowDeleted = 0 OR RowDeleted IS NULL)
    			ORDER BY JobID
    		END
    	Else If Not(@JobLocation Is Null)
    		BEGIN
    			SELECT     JobID
    			FROM         dbo.Sopa
    			WHERE     (CompanyID = @CompanyID) AND (JobLocation LIKE '%' + @JobLocation + '%') 
    				AND (RowDeleted = 0 OR RowDeleted IS NULL)
    			ORDER BY JobID
    		END
    --there are more IFs to cover all parameters
    I send always only 1 parameter with Not Null value.
    So, only one select statement is used when SP works.
    I was requested to add the ability to use as many parameters as the user wants.
    I think that UNION should be used in this case.
    How can I add second, third etc SELECT depending on parameters with not Null values.?

    Thank you

  2. #2
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: SQL Server UNION

    Did you try to just replace all your "Else If" with just "if"?
    So just return the multiple result sets?
    Victor Nijegorodov

  3. #3
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: SQL Server UNION

    You can also first create the SQL script (with UNION and depending on the parameters passed in the SP) and the execute it using sp_executesql
    Victor Nijegorodov

  4. #4
    Join Date
    Jan 2013
    Posts
    90

    Re: SQL Server UNION

    More help is required.
    How to add dependancy on parameters?
    Thank you very much

  5. #5
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: SQL Server UNION

    Quote Originally Posted by chapran View Post
    More help is required.
    How to add dependancy on parameters?
    Thank you very much
    Something like (pseudo-code, not tested!):
    Code:
    Declare @sql nvarchar(max)
    
    
    If @DontDisplayArchivedData=0
    	BEGIN
    	If Not(@JobNumber Is Null)
    		BEGIN
    			SET @sql = N'
    				SELECT     JobID
    				FROM         dbo.Sopa
    				WHERE     (CompanyID = @CompanyID) AND (JobNumber LIKE '%' + @JobNumber + '%') AND 
    					(RowDeleted = 0 OR RowDeleted IS NULL)
    				ORDER BY JobID
    				'
    		END
    	If Not(@JobName Is Null)
    		BEGIN
    			IF @sql Is Not Null
    				SET @sql = @sql + N' UNION '
    			ELSE 
    				SET @sql = N''
    			END
    			
    			SET @sql = @sql + N'
    				SELECT     JobID
    				FROM         dbo.Sopa
    				WHERE     (CompanyID = @CompanyID) AND (JobName LIKE '%' + @JobName + '%') AND (RowDeleted = 0 OR RowDeleted IS NULL)
    				ORDER BY JobID
    				'
    		END
    	If Not(@JobLocation Is Null)
    		BEGIN
    			...
    		END
    
    	END
    ...
    IF @sql Is Not Null
    BEGIN
    	sp_executesql @sql
    END
    Victor Nijegorodov

  6. #6
    Join Date
    Jan 2013
    Posts
    90

    Re: SQL Server UNION

    Wow! So elegant! Thank you very much!

  7. #7
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: SQL Server UNION

    Quote Originally Posted by chapran View Post
    Wow! So elegant! Thank you very much!
    You are welcome!
    However, note: I wrote it from scratch, without testing and I could also something forget, cause I don't use SQL Server since at least five years...
    Victor Nijegorodov

  8. #8
    Join Date
    Jan 2013
    Posts
    90

    Re: SQL Server UNION

    You gave me a great scenario. Your code doesn't work, but it is my part to adjust. I beleive it will work.
    Thank you very much again.

  9. #9
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: SQL Server UNION

    Quote Originally Posted by chapran View Post
    You gave me a great scenario. Your code doesn't work, but it is my part to adjust. I beleive it will work.
    Thank you very much again.
    Yeah, I see:
    Code:
    SET @sql = N'
    				SELECT     JobID
    				FROM         dbo.Sopa
    				WHERE     (CompanyID = @CompanyID) AND (JobNumber LIKE '%' + @JobNumber + '%') AND 
    					(RowDeleted = 0 OR RowDeleted IS NULL)
    				ORDER BY JobID
    				'
    It should be perhaps, double-quoted:
    Code:
    SET @sql = N'
    				SELECT     JobID
    				FROM         dbo.Sopa
    				WHERE     (CompanyID = @CompanyID) AND (JobNumber LIKE ''%'' + @JobNumber + ''%'') AND 
    					(RowDeleted = 0 OR RowDeleted IS NULL)
    				ORDER BY JobID
    				'
    or rewritten some other way...
    Last edited by VictorN; October 15th, 2018 at 02:45 PM.
    Victor Nijegorodov

  10. #10
    Join Date
    Jan 2013
    Posts
    90

    Re: SQL Server UNION

    It looks like this now and it works. I just need to rewrite all IFs:

    If @DontDisplayArchivedData = 0
    BEGIN
    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
    print @sql

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

  11. #11
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: SQL Server UNION

    Quote Originally Posted by chapran View Post
    It looks like this now and it works. I just need to rewrite all IFs:

    Code:
    If @DontDisplayArchivedData = 0
    	BEGIN
    	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
    	print  @sql
    
    	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) '
    Please, don't forget adding code tags!
    Victor Nijegorodov

  12. #12
    Join Date
    Jan 2013
    Posts
    90

    Re: SQL Server UNION

    By some reason the button for code tag disappeared from my page. Next time I'll try to add manually.
    One more thing:
    The request was made more complex - users want to have the ability to set search for either "AND" and "OR". The current procedure works with "OR". How to write the select with variable number of parameters? For instance:
    "Select jobs where JobNumber='1111-2222' AND CustomerID = 111" and may be continued with " AND SalesmanID = 22"

  13. #13
    Join Date
    Jan 2013
    Posts
    90

    Re: SQL Server UNION

    Quote Originally Posted by chapran View Post
    By some reason the button for code tag disappeared from my page. Next time I'll try to add manually.
    One more thing:
    The request was made more complex - users want to have the ability to set search for either "AND" and "OR". The current procedure works with "OR". How to write the select with variable number of parameters? For instance:
    "Select jobs where JobNumber='1111-2222' AND CustomerID = 111" and may be continued with " AND SalesmanID = 22"
    I found solution.

  14. #14
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: SQL Server UNION

    Quote Originally Posted by vanphong11021 View Post
    tặng tui tham dự với nha
    What did you mean?
    Victor Nijegorodov

  15. #15
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: SQL Server UNION

    Quote Originally Posted by chapran View Post
    I found solution.
    Super!
    Could you share it?
    Victor Nijegorodov

Page 1 of 2 12 LastLast

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured