August 24, 2009 at 4:24 pm
Currently, I have a stored proc with an array parameter. The way I have is using IN
select *
from programs
where programid in @Array
This does "OR" between the array elements. Programid=1 or Programid=2, etc
Now, I want to say "AND" between the array elements.
Based on the subjects give me the programid.
Data:
ProgramId SubjectAreaId
1 1
1 2
1 4
1 8
2 3
2 5
2 8
3 2
3 8
If the array passed in is (1,2,4,8), the result would be programid 1 because it is the only one that has 1 and 2 and 4 and 8.
If I use the "in @array" I get programid of 1,2,3 because it is an "OR" search
Any suggestions would be appreciated!!!
August 24, 2009 at 7:04 pm
That's not how SQL logic works. SQL returns ROWS that meet the given criteria, but your criteria isn't satisfied by a single row. You would need multiple rows to determine if your criteria is matched.
You're either going to need to PIVOT (or equivalent) your table to get all of the necessary information into one row or UNPIVOT your argument to be able to match multiple records.
You haven't given us enough information to determine which is the best approach for what you are trying to accomplish.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 24, 2009 at 9:20 pm
Please show us your *actual* stored procedure that you are currently using. The "OR" example that you showed us would not work. If you can do that then I am confident that we can change it into an "AND" proc for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2009 at 9:52 am
This seems a little clunky, but I think it does what you want.
declare @array table (value int primary key)
insert into @array
select 1 union all
select 2 union all
select 4 union all
select 8
-- for a production table this would run best with a covering index built on (SubjectAreaID,ProgramID)
declare @sample table (ProgramId int, SubjectAreaId int)
insert into @sample
select 1, 1 union all
select 1, 2 union all
select 1, 4 union all
select 1, 8 union all
select 2, 3 union all
select 2, 5 union all
select 2, 8 union all
select 3, 2 union all
select 3, 8
----------------------------------------------------------------------------------------------
-- Everything above this line was just to set up sample data. Solution follows:
----------------------------------------------------------------------------------------------
declare @rc int
select @rc = COUNT(distinct value) from @array
;with cte1 as -- this shows only rows that match the array values
(select ProgramId, SubjectAreaId
from @sample s
join @array a on s.SubjectAreaId = a.value
)
,cte2 as -- this counts the distinct values for each program id to test against the array count @rc
(select programID, count(distinct subjectAreaID) as matchCount from cte1 group by programID)
select programID from cte2
where matchCount = @rc
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 11:59 am
Two ways you can do it:
1) If you can add an extra parameter to your stored procedure then the following is a very simple solution, similar to what was suggested earlier
DECLARE @program TABLE
(
ProgramId INT, SubjectAreaId INT
)
INSERT INTO @program SELECT 1,1
INSERT INTO @program SELECT 1, 2
INSERT INTO @program SELECT 1 ,4
INSERT INTO @program SELECT 1 ,8
INSERT INTO @program SELECT 2 ,3
INSERT INTO @program SELECT 2 ,5
INSERT INTO @program SELECT 2 ,8
INSERT INTO @program SELECT 3 ,2
INSERT INTO @program SELECT 3 ,8;
DECLARE @nCounts INT
SET @nCounts = 4
SELECT ProgramId FROM @program
WHERE SubjectAreaId IN (1,2,4,8)
GROUP By ProgramId
HAVING COUNT(*) = @nCounts
@nCounts is the parameter you need to pass, this should be the count of the elements in your array.
2) If you don't want to change the parameters to stored procedure, write a function which calculates the element in the array passed and do the same solution again
August 25, 2009 at 1:35 pm
If you are going to hard code the WHERE clause to be (1,2,4,8), why don't you just go ahead and say = 4?
What if someone makes a typo and has "2" in the array twice. What if there suplicate rows in the table to be searched that have the same SubjectAreaID and ProgramID?
Even though it slows the query down, I really think the DISTINCT keywords need to be included.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 2:21 pm
This is pointless anyway. he OP is not answering any question and what they originally posted could not have worked. We really cannot do anything to help them until that give us some more information.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2009 at 2:57 pm
Oh yeah.... I tend to forget about the purpose of all this. I just play for love of the game.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 4:14 pm
Sorry for not getting back sooner....
Here's the actual sproc:
ALTER PROCEDURE
[Out].[programs_programsearch_select_by_various]
@CountryIDs varchar(max) = NULL,
@OptionIDs varchar(max) = NULL,
@SubjectAreaIDs varchar(max) = NULL,
@LanguageOfInstructionID int = NULL,
@GPA decimal(4,2)=null,
@MinLanguagePrerequisiteQrtrs int = null,
@ClassLevelIDs varchar(MAX) = NULL,
@IsEligibleForGraduates Bit = Null,
@ProgramId Int = null
AS
SELECT DISTINCT
pi.CountryID,
c.Name as Country,
p.ProgramID,
p.Name as Program,
pp.PageName,
pt.ProgramSearchName,
p.gpa
FROMOut.Programsp
INNER JOINOut.v_packages_packagesprogramsoptions_programsoptions_programsvppppONvpppp.ProgramID = p.ProgramID
INNER JOINOut.ProgramsPartnerInstitutionsppiONppi.ProgramID = p.ProgramID
INNER JOINOut.ProgramsPartnerInstitutionsOptionsppioONppio.ProgramPartnerInstitutionID = ppi.ProgramPartnerInstitutionID
and ppio.ProgramOptionID = vpppp.ProgramOptionID
INNER JOINdbo.PartnerInstitutionspiONpi.PartnerInstitutionID = ppi.PartnerInstitutionID
INNER JOINOut.ProgramTypesptonpt.ProgramTypeID = p.ProgramTypeID
INNER JOINdbo.CountriescONc.CountryID = pi.CountryID
LEFT JOINdbo.fn_Parse_int_array(@CountryIDs)pCountryIDsONpi.CountryID = pCountryIDs.Element
INNER JOINOut.OptionsoONo.OptionID = vpppp.OptionID
LEFT JOINdbo.fn_Parse_int_array(@OptionIDs)pOptionIDsONvpppp.OptionID = pOptionIDs.Element
LEFT JOINOut.ProgramsPartnerInstitutionsSubjectAreas ppisaON ppisa.ProgramPartnerInstitutionID = ppi.ProgramPartnerInstitutionID
LEFT JOINAca.SubjectAreassaONsa.SubjectAreaID = ppisa.SubjectAreaID
LEFT JOINdbo.fn_Parse_int_array(@SubjectAreaIDs) pSubjectAreaIDsONppisa.SubjectAreaID = pSubjectAreaIDs.Element
LEFT JOINOut.ProgramsLanguagesplONpl.ProgramID = p.ProgramID
LEFT JOINdbo.LanguagesOfInstructionloionloi.LanguageOfInstructionID = pl.LanguageOfInstructionID
LEFT JOINOut.ProgramLanguagePrerequisitesplponplp.ProgramLanguageID = pl.ProgramLanguageID
LEFT JOINOut.LanguagePrerequisiteslpminonlpmin.LanguagePrerequisiteID=plp.MinimumLanguagePrerequisiteID
LEFT JOINOut.LanguagePrerequisiteslpmaxonlpmax.LanguagePrerequisiteID=plp.MaximumLanguagePrerequisiteID
LEFT JOINweb.ProgramPagespponpp.ProgramID=p.ProgramID
left joinOut.ProgramsClassLevelspclon pcl.ProgramID=p.ProgramID
LEFT JOINdbo.fn_Parse_int_array(@ClassLevelIDs)pClLevelIDsONpcl.ClassLevelID = pClLevelIDs.Element
inner joinout.PackagespckON pck.PackageId = vpppp.PackageId
ANDpck.IsActiveOnlineApplication=1
WHERE vpppp.IsSearchable = 1
and p.IsActiveOnlineApplication = 1
and ppio.IsActive = 1
and ppi.IsActive = 1
and (@CountryIDs IS NULL OR pCountryIDs.Element IS NOT NULL)
and (@OptionIDs IS NULL OR pOptionIDs.Element IS NOT NULL)
and (@SubjectAreaIDs IS NULL OR pSubjectAreaIDs.Element IS NOT NULL)
and (@LanguageOfInstructionID is null or pl.LanguageOfInstructionID=@LanguageOfInstructionID)
and (@GPA is null or p.gpa<=@GPA)
and (@ProgramId is null or p.programid = @ProgramId)
and ((@ClassLevelIds is null and @IsEligibleForGraduates is null)
OR pClLevelIDs.element is not null or p.IsEligibleForGraduates=@IsEligibleForGraduates)
and (
@MinLanguagePrerequisiteQrtrs is null
or (
(lpmin.Quarters <0 or lpmin.Quarters is null or lpmin.Quarters <= @MinLanguagePrerequisiteQrtrs)
and (lpmax.Quarters = @MinLanguagePrerequisiteQrtrs)
)
)
and (
(@LanguageOfInstructionID!=10 or @LanguageOfInstructionID is NULL) -- not "English"
OR (
(pl.LanguageOfInstructionID = 10 and
not exists (select * FROM Out.ProgramsLanguages
WHERE ProgramID = pl.ProgramID
AND LanguageOfInstructionID 10
AND IsOptional=0))
or (pl.LanguageOfInstructionID = 10 and
exists (select * FROM Out.ProgramsLanguages pl2
left join Out.ProgramLanguagePrerequisites plr on plr.ProgramLanguageID = pl2.ProgramLanguageID
WHERE ProgramID = pl.ProgramID
AND (plr.MinimumLanguagePrerequisiteID=1 or plr.ProgramLanguagePrerequisiteID is null)
AND LanguageOfInstructionID 10
AND IsOptional=0))
or (pl.LanguageOfInstructionID 10 and pl.IsOptional= 1 and
exists (select * FROM Out.ProgramsLanguages
where ProgramID=pl.ProgramID and LanguageOfInstructionID=10))
or (pl.LanguageOfInstructionID 10 and pl.IsOptional= 0 and
exists (select * FROM Out.ProgramsLanguages pl
WHERE ProgramID = pl.ProgramID
AND LanguageOfInstructionID = 10)
and exists (select * from out.ProgramLanguagePrerequisites plpr
where (plpr.MinimumLanguagePrerequisiteID=1
and ProgramLanguageID= pl.ProgramLanguageID)))
)
)
ORDER BY c.Name, p.Name
return 0
August 25, 2009 at 4:40 pm
Bob Hovious (8/25/2009)
If you are going to hard code the WHERE clause to be (1,2,4,8), why don't you just go ahead and say = 4?What if someone makes a typo and has "2" in the array twice. What if there suplicate rows in the table to be searched that have the same SubjectAreaID and ProgramID?
Even though it slows the query down, I really think the DISTINCT keywords need to be included.
First of all everything is hard coded in the example even the values in the table, it was just an "EXAMPLE" which I was trying to show, and again it was an example to say 4, which would be a parameter as I said.
You are right, that the example would result empty set, incase somebody mistypes, if somebody mistypes the input, i would prefer the input to be corrected :). Validation should not be in stored procedures.
Just my 2 cents....
August 25, 2009 at 5:36 pm
that was for test purposes, the (1,2,4,8) comes in as a string. I use a "where-in" clause not the hard code.
August 25, 2009 at 6:11 pm
Yes, I understand that it is an example. But it is inconsistent to hard code a set of values and then to say that a count of those values is being passed in as a parameter. If you are going to pass a set of values to be tested against, it seems pointless to also pass a count of the values. SQL can get the count with a cte or a subquery without the need of separate function.
Second, although validation should certainly be done on the front end, having a procedure catch errors and deal with them (or return the error to the calling application) ensures consistency if procedures are used by more than one application. In addition, people in forums often raise issues like this, because the original poster may not have thought of everything when posting their sample data.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 7:46 pm
Bob Hovious (8/25/2009)
Yes, I understand that it is an example. But it is inconsistent to hard code a set of values and then to say that a count of those values is being passed in as a parameter. If you are going to pass a set of values to be tested against, it seems pointless to also pass a count of the values. SQL can get the count with a cte or a subquery without the need of separate function.Second, although validation should certainly be done on the front end, having a procedure catch errors and deal with them (or return the error to the calling application) ensures consistency if procedures are used by more than one application. In addition, people in forums often raise issues like this, because the original poster may not have thought of everything when posting their sample data.
Ok, I thought it was implicit that values are being passed as an array, that was just used in where clause so that the original poster can relate to it. Function was proposed, so that, if similar need comes up in future we don't have to replicate the code. Now, how you implement the function is upto you.
Passing count was proposed, so that it could be easier for some users, as it is easy to know the count of elements in an array at the stored procedure call then to write a CTE or subquery to calculate inside the procedure. It depends on your individual comfort level.
I agree procedure could catch errors, but the question is what type of error, should it catch data inconsistency errors or data exceptions or any other exceptions. I prefer catching exceptions.
Point well taken about original poster might not know what they want to ask.
August 25, 2009 at 8:22 pm
Upon reflection, I must concede that where work (such as taking a count) can be done on a web application prior to calling a procedure, it takes the load off the SQL server, which,most would deem to be a good thing. The problem is that the procedure is now dependent on the application programmer(s) to get the count right and passed correctly or it will return a false negative, not even an error. That possibility troubles me.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 27, 2009 at 11:38 am
Finally, got back to it and your suggestion worked!!! It was very slick!
Thank you so much!!!!!!!:-D
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply