Solved

How to return records from Pivot View with multiple criteria

Posted on 2012-04-13
16
340 Views
Last Modified: 2012-08-14
I am trying to figure out a way to return records from a PivotView based on multiple selections from two list boxes.  Below is the code for retrieving records based on selections made from two comboboxes (See Original code below.  It worked fine, however the requirements have changed and now management wants to be able to make multiple selections from two list boxes so that essentially @Review and @Status would each contain a comma delimited string each string consisting of multiple reviews and statuses respectively.  I think what need to do is to build the where clause via the following pseudocode.  Basically I need some brainstorming

--------------------Psuedocode
For Each Review in @Review
       ADD the following to the Where
            Review1 = ISNULL(@Review, Review1) OR
            Review2 = ISNULL(@Review, Review2) OR
            Review3 = ISNULL(@Review, Review3) OR
            Review4 = ISNULL(@Review, Review4) OR
            Review5 = ISNULL(@Review, Review5) OR
            Review6 = ISNULL(@Review, Review6) OR
            Review7 = ISNULL(@Review, Review7) OR
            Review8 = ISNULL(@Review, Review8)
NEXT

For Each Status in @Review
      Add the following to the where clause
            Status1 = ISNULL(@Status, Status1) OR
            Status2 = ISNULL(@Status, Status2) OR
            Status3 = ISNULL(@Status, Status3) OR
            Status4 = ISNULL(@Status, Status4) OR
            Status5 = ISNULL(@Status, Status5) OR
            Status6 = ISNULL(@Status, Status6) OR
            Status7 = ISNULL(@Status, Status7) OR
            Status8 = ISNULL(@Status, Status8)
NEXT

------------------------------------ORIGINAL CODE-----------------------------------
CREATE PROC [dbo].[spRetrieveByReviewOrStatusREV]
@Review AS varchar(500) = NULL,
@Status AS varchar(500) = NULL
AS




SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
            Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
FROM [dbo].[vwPivotedReviewsRevised]
WHERE
(
Review1 = ISNULL(@Review, Review1) OR
Review2 = ISNULL(@Review, Review2) OR
Review3 = ISNULL(@Review, Review3) OR
Review4 = ISNULL(@Review, Review4) OR
Review5 = ISNULL(@Review, Review5) OR
Review6 = ISNULL(@Review, Review6) OR
Review7 = ISNULL(@Review, Review7) OR
Review8 = ISNULL(@Review, Review8)
)
AND
(
Status1 = ISNULL(@Status, Status1) OR
Status2 = ISNULL(@Status, Status2) OR
Status3 = ISNULL(@Status, Status3) OR
Status4 = ISNULL(@Status, Status4) OR
Status5 = ISNULL(@Status, Status5) OR
Status6 = ISNULL(@Status, Status6) OR
Status7 = ISNULL(@Status, Status7) OR
Status8 = ISNULL(@Status, Status8)
)
      

ORDER BY USI
GO
0
Comment
Question by:chtullu135
  • 11
  • 5
16 Comments
 

Author Comment

by:chtullu135
ID: 37844704
I've been working on it and I think I'm making some headway.


CREATE PROC [dbo].[spRetrieveByReviewOrStatusREV]
@Review AS varchar(500) = NULL,
@Status AS varchar(500) = NULL
AS

DECLARE @R varchar(max)
SET @R = @Review
SET @R = @R + ','

DECLARE @S varchar(max)
set @S = @Status
set @S = @S + ','

Declare @SqlString as varchar(max)
SET @SqlString =      'SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised] WHERE '
WHILE len(@R)>0
BEGIN
@SqlString = @SqlString +
                                          (
                                          Review1 = ISNULL(@R, Review1) OR
                                          Review2 = ISNULL(@R, Review2) OR
                                          Review3 = ISNULL(@R, Review3) OR
                                          Review4 = ISNULL(@R, Review4) OR
                                          Review5 = ISNULL(@R, Review5) OR
                                          Review6 = ISNULL(@R, Review6) OR
                                          Review7 = ISNULL(@R, Review7) OR
                                          Review8 = ISNULL(@R, Review8)
                                          )

END
AND
WHILE len(@S)>0
BEGIN
@SqlString = @SqlString + 'AND'
                                          (
                                          Status1 = ISNULL(@S, Status1) OR
                                          Status2 = ISNULL(@S, Status2) OR
                                          Status3 = ISNULL(@S, Status3) OR
                                          Status4 = ISNULL(@S, Status4) OR
                                          Status5 = ISNULL(@S, Status5) OR
                                          Status6 = ISNULL(@S, Status6) OR
                                          Status7 = ISNULL(@S, Status7) OR
                                          Status8 = ISNULL(@S, Status8)
                                          )
END      

ORDER BY USI
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
0
 

Author Comment

by:chtullu135
ID: 37844928
To make it simpler to code, I'm going to work on one section at a time.  I will be first working on the Review portion

ALTER PROC [dbo].[spRetrieveByReviewOrStatusREV]
@Review AS varchar(500) = NULL,
@Status AS varchar(500) = NULL
AS
DECLARE @R varchar(max)
SET @R = @Review
SET @R = @R + ','

Declare @SqlFrom as varchar(max)
SET @SqlFrom = 'SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised] WHERE Review'
DECLARE @Where as varchar(max)

WHILE len(@R)>0
BEGIN
      SET @Where = (
                                          Review1 = ISNULL(@R, Review1) OR
                                          Review2 = ISNULL(@R, Review2) OR
                                          Review3 = ISNULL(@R, Review3) OR
                                          Review4 = ISNULL(@R, Review4) OR
                                          Review5 = ISNULL(@R, Review5) OR
                                          Review6 = ISNULL(@R, Review6) OR
                                          Review7 = ISNULL(@R, Review7) OR
                                          Review8 = ISNULL(@R, Review8))

END

DECLARE @Sql as varchar(max)
SET @Sql = @SqlFrom + ' ' + @Where
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37847423
>>essentially @Review and @Status would each contain a comma delimited string each string consisting of multiple reviews and statuses respectively<<

If I understood correctly

you can just try this where clause without the dynamic SQL there:

WHERE
(
',' + Review1 + ',' LIKE '%,' + @Review ',%' OR
',' + Review2 + ',' LIKE '%,' + @Review ',%' OR
',' + Review3 + ',' LIKE '%,' + @Review ',%' OR
',' + Review4 + ',' LIKE '%,' + @Review ',%' OR
',' + Review5 + ',' LIKE '%,' + @Review ',%' OR
',' + Review6 + ',' LIKE '%,' + @Review ',%' OR
',' + Review7 + ',' LIKE '%,' + @Review ',%' OR
',' + Review8 + ',' LIKE '%,' + @Review ',%'
)
AND
(
',' + Status1 + ',' LIKE '%,' + @Status1 ',%' OR
',' + Status2 + ',' LIKE '%,' + @Status2 ',%' OR
',' + Status3 + ',' LIKE '%,' + @Status3 ',%' OR
',' + Status4 + ',' LIKE '%,' + @Status4 ',%' OR
',' + Status5 + ',' LIKE '%,' + @Status5 ',%' OR
',' + Status6 + ',' LIKE '%,' + @Status6 ',%' OR
',' + Status7 + ',' LIKE '%,' + @Status7 ',%' OR
',' + Status8 + ',' LIKE '%,' + @Status8 ',%'
)
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37847428
or if you create the function below you can use it in your WHERE clause like this

WHERE
(
Review1 in (select value from dbo.parmstolist(@Review)) OR
Review2 in (select value from dbo.parmstolist(@Review)) OR
Review3 in (select value from dbo.parmstolist(@Review)) OR
Review4 in (select value from dbo.parmstolist(@Review)) OR
Review5 in (select value from dbo.parmstolist(@Review)) OR
Review6 in (select value from dbo.parmstolist(@Review)) OR
Review7 in (select value from dbo.parmstolist(@Review)) OR
Review8 in (select value from dbo.parmstolist(@Review))
)
AND (
Status1 in (select Value from dbo.parmstolist(@Status)) OR
Status2 in (select Value from dbo.parmstolist(@Status)) OR
Status3 in (select Value from dbo.parmstolist(@Status)) OR
Status4 in (select Value from dbo.parmstolist(@Status)) OR
Status5 in (select Value from dbo.parmstolist(@Status)) OR
Status6 in (select Value from dbo.parmstolist(@Status)) OR
Status7 in (select Value from dbo.parmstolist(@Status)) OR
Status8 in (select Value from dbo.parmstolist(@Status))
)



-----------------------------------
create FUNCTION [dbo].[ParmsToList] (@Parameters varchar(8000), @delimiter varchar(10) )
returns @result TABLE (Value varchar(500), rn int identity)
AS
begin
declare @dx varchar(9)
-- declare @loops int
--set @loops = 0

DECLARE @TempList table
(
Value varchar(500)
)

if @delimiter is null set @delimiter = ' '
if len(@delimiter) < 1 set @delimiter = ' '
set @dx = left(@delimiter, case when @delimiter = ' ' then 1 else len(@delimiter) end -1)

DECLARE @Value varchar(8000), @Pos int

SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)

IF REPLACE(@Parameters, @delimiter, @dx) <> ''
BEGIN
WHILE @Pos > 0 -- AND @Loops < 100
BEGIN
--set @loops = @loops + 1
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
IF @Value <> ''
BEGIN
INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
END
SET @Parameters = SUBSTRING(@Parameters, @Pos+ case when @delimiter = ' ' then 1 else len(@delimiter) end, 8000)
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)

END
END
INSERT @result
SELECT value
FROM @TempList
RETURN
END
0
 

Author Comment

by:chtullu135
ID: 37848335
Basically @Review would contain the data in the following format.
Loss Mitigation, Complaints, Bankruptcy
@Status would contain the respective data in the following format
345, 566, 2             where the numbers represent the statusid code,  I was using the status names but figured using the id numbers would improve performance a little bit more
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37848743
So have you tried my suggestions?
0
 

Author Comment

by:chtullu135
ID: 37849238
I'll be trying it tomorrow at work.  I'm really interested in the performance gain.
0
 

Author Comment

by:chtullu135
ID: 37852098
Hello ralmada,

The dynamic sql method though while it works is way to messy.  I've been trying your method that employs a function however I am getting the following error message

Msg 102, Level 15, State 1, Procedure spRetrieveByReviewOrStatusRev3, Line 12
Incorrect syntax near ','.

Now I did test the function via the following and it worked fine, so I'm not sure what is going on

SELECT * from dbo.ParmsToList('Complaints,Bankruptcy', ',')
CREATE PROC [dbo].[spRetrieveByReviewOrStatusRev3]
@Review AS varchar(max) = NULL,
@Status as varchar(max) = NULL
AS
DECLARE @Delimiter As varchar(10)
SET @Delimiter = ','
SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised]
WHERE
(
Review1 in (select value from dbo.parmstolist(@Review), ',') OR
Review2 in (select value from dbo.parmstolist(@Review), ',') OR
Review3 in (select value from dbo.parmstolist(@Review), ',') OR
Review4 in (select value from dbo.parmstolist(@Review), ',') OR
Review5 in (select value from dbo.parmstolist(@Review), ',') OR
Review6 in (select value from dbo.parmstolist(@Review), ',') OR
Review7 in (select value from dbo.parmstolist(@Review), ',') OR
Review8 in (select value from dbo.parmstolist(@Review), ',')
)
AND (
Status1 in (select Value from dbo.parmstolist(@Status), ',') OR
Status2 in (select Value from dbo.parmstolist(@Status), ',') OR
Status3 in (select Value from dbo.parmstolist(@Status), ',') OR
Status4 in (select Value from dbo.parmstolist(@Status), ',') OR
Status5 in (select Value from dbo.parmstolist(@Status), ',') OR
Status6 in (select Value from dbo.parmstolist(@Status), ',') OR
Status7 in (select Value from dbo.parmstolist(@Status), ',') OR
Status8 in (select Value from dbo.parmstolist(@Status), ',')
)
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:chtullu135
ID: 37852128
Never mind I found the problem,
Review1 in (select value from dbo.parmstolist(@Review), ',') should be
Review1 in (select value from dbo.parmstolist(@Review, ','))
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37852295
yep, extra brackets there

same with @Status
0
 

Author Comment

by:chtullu135
ID: 37852316
It's working.  However, I want to account for when one or both input parameters to the stored procedure are null
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 37852406
just add this

WHERE
(
@Review is null OR
Review1 in (select value from dbo.parmstolist(@Review, ',') OR
Review2 in (select value from dbo.parmstolist(@Review, ',') OR
Review3 in (select value from dbo.parmstolist(@Review, ',') OR
Review4 in (select value from dbo.parmstolist(@Review, ',') OR
Review5 in (select value from dbo.parmstolist(@Review, ',') OR
Review6 in (select value from dbo.parmstolist(@Review, ',') OR
Review7 in (select value from dbo.parmstolist(@Review, ',') OR
Review8 in (select value from dbo.parmstolist(@Review, ',')
)
AND (
@Status is null OR
Status1 in (select Value from dbo.parmstolist(@Status, ',') OR
Status2 in (select Value from dbo.parmstolist(@Status, ',') OR
Status3 in (select Value from dbo.parmstolist(@Status, ',') OR
Status4 in (select Value from dbo.parmstolist(@Status, ',') OR
Status5 in (select Value from dbo.parmstolist(@Status, ',') OR
Status6 in (select Value from dbo.parmstolist(@Status, ',') OR
Status7 in (select Value from dbo.parmstolist(@Status, ',') OR
Status8 in (select Value from dbo.parmstolist(@Status, ',')
)
0
 

Author Comment

by:chtullu135
ID: 37852437
This is what I'm thinking to deal with nulls
ALTER PROC [dbo].[spRetrieveByReviewOrStatusRev3]
@Review AS varchar(max) = NULL,
@Status as varchar(max) = NULL
AS
DECLARE @Delimiter As varchar(10)
SET @Delimiter = ','
SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised]

WHERE

(
Review1 in (select value from dbo.parmstolist(@Review,',')) OR
Review2 in (select value from dbo.parmstolist(@Review,',')) OR
Review3 in (select value from dbo.parmstolist(@Review,',')) OR
Review4 in (select value from dbo.parmstolist(@Review,',')) OR
Review5 in (select value from dbo.parmstolist(@Review,',')) OR
Review6 in (select value from dbo.parmstolist(@Review,',')) OR
Review7 in (select value from dbo.parmstolist(@Review,',')) OR
Review8 in (select value from dbo.parmstolist(@Review,','))
)
AND (

IF @Status IS NOT NULL THEN
      BEGIN
            Status1 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status2 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status3 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status4 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status5 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status6 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status7 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status8 in (select Value from dbo.parmstolist(@Status,','))
      END
ELSE
      BEGIN
            Status1 LIKE ' % ' OR
            Status2 LIKE ' % ' OR
            Status3 LIKE ' % ' OR
            Status4 LIKE ' % ' OR
            Status5 LIKE ' % ' OR
            Status6 LIKE ' % ' OR
            Status7 LIKE ' % ' OR
            Status8 LIKE ' % ' OR
      END
)
0
 

Author Comment

by:chtullu135
ID: 37852441
I just saw what you had posted for dealing with nulls.  It can't get any simpler - thanks
0
 

Author Comment

by:chtullu135
ID: 37852485
Everything is working.  Although the dynamic sql method worked, the code you posted was much simpler and is easier to maintain.  I've learned quite a bit.  Thanks again.
0
 

Author Closing Comment

by:chtullu135
ID: 37852493
Thanks for your help.  I've learned a lot.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now