-
October 15th, 2018, 12:32 PM
#1
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
-
October 15th, 2018, 01:07 PM
#2
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
-
October 15th, 2018, 01:14 PM
#3
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
-
October 15th, 2018, 01:48 PM
#4
Re: SQL Server UNION
More help is required.
How to add dependancy on parameters?
Thank you very much
-
October 15th, 2018, 02:17 PM
#5
Re: SQL Server UNION
Originally Posted by chapran
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
-
October 15th, 2018, 02:19 PM
#6
Re: SQL Server UNION
Wow! So elegant! Thank you very much!
-
October 15th, 2018, 02:34 PM
#7
Re: SQL Server UNION
Originally Posted by chapran
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
-
October 15th, 2018, 02:36 PM
#8
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.
-
October 15th, 2018, 02:43 PM
#9
Re: SQL Server UNION
Originally Posted by chapran
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
-
October 15th, 2018, 03:27 PM
#10
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) '
-
October 15th, 2018, 03:33 PM
#11
Re: SQL Server UNION
Originally Posted by chapran
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
-
October 15th, 2018, 05:30 PM
#12
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"
-
October 16th, 2018, 09:27 AM
#13
Re: SQL Server UNION
Originally Posted by chapran
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.
-
October 16th, 2018, 09:43 AM
#14
Re: SQL Server UNION
Originally Posted by vanphong11021
tặng tui tham dự với nha
What did you mean?
Victor Nijegorodov
-
October 16th, 2018, 09:44 AM
#15
Re: SQL Server UNION
Originally Posted by chapran
I found solution.
Super!
Could you share it?
Victor Nijegorodov
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|