March 16, 2009 at 7:34 am
I am currently developing a user control in asp.net. What I have is a list of 16 possible filtering selections. I need to have my current stored procedure actually filter and only output the records that have all the selected filters. What currently is happening is that the stored procedure is pushing back every record any of the selected filters are in, whereas I need this to only include the records that match all the selected filters. Obviously I need to replace my IN statement, but I have tried a few things and it hasn't worked. My variable is a comma delimited value, ie @TagID = '1', '9'. and so on when passed from the filter selection(asp.net page) to the stored procedure.
ALTER PROCEDURE [dbo].[GetScholarshipsbyTag]
-- Add the parameters for the stored procedure here
@TagID as varchar(50)
AS
DECLARE @sql varchar(4000)
SET @sql =
'Select s.ScholarshipID
, s.Scholarship
, s.Institution
, s.Amount
, s.Deadline
, s.Contact
, s.Requirements
From dbo.Tag t
, dbo.ScholarshipTag st
, dbo.Scholarship s
Where t.tagid = st.tagid
AND s.ScholarshipID = st.scholarshipID
AND st.tagid IN(' + @TagID + ')'
EXECUTE (@sql)
March 16, 2009 at 7:47 am
Try the following and see if that works for you:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[DelimitedSplit] (
@pString varchar(max),
@pDelimiter char(1)
)
returns table
as
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select
row_number() over (order by N) as N
from
a4),
ItemSplit(
ItemOrder,
Item
) as (
SELECT
N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(',',@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = ',' --Notice how we find the comma
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit;
GO
Select
s.ScholarshipID
, s.Scholarship
, s.Institution
, s.Amount
, s.Deadline
, s.Contact
, s.Requirements
From
dbo.Tag t
inner join dbo.ScholarshipTag st
on (t.tagid = st.tagid)
inner join dbo.Scholarship s
on (s.ScholarshipID = st.scholarshipID)
inner join dbo.DelimitedSplit ds
on (st.tagid = ds.item)
March 16, 2009 at 8:01 am
Got it to work with the following code:
ALTER PROCEDURE [dbo].[GetScholarshipsbyTag]
-- Add the parameters for the stored procedure here
@TagID as varchar(50)
AS
DECLARE @sql varchar(4000)
,@tagcount int
-- Calculate the list of individual comma separated items in the parameter
SET@tagcount = ISNULL(LEN(@tagID) - LEN(REPLACE(@tagID, ',','')), 0) + 1
SET @sql = '
SELECT s.ScholarshipID
,s.Scholarship
,s.Institution
,s.Amount
,s.Deadline
,s.Contact
,s.Requirements
FROM
dbo.Scholarship s
INNER JOIN
(SELECT ScholarshipId, COUNT(*) as TagCount
FROM dbo.ScholarshipTag
WHERE tagid IN (' + @TagID + ')
GROUP BY ScholarshipId
HAVING COUNT(*) = ' + CAST(@tagcount AS varchar(10)) + '
) st ON st.scholarshipID = s.ScholarshipID'
EXECUTE (@sql)
GO
March 16, 2009 at 10:41 am
Just curious if you took a close look at the code I provided. It would actually allow you to emilinate the dynamic sql you are using in your stored procedure. This could also improve the stored procedure's performance.
March 16, 2009 at 10:44 am
I did, however to be honest it popped an error for me and my co worker IM'd me the other code and it worked. So moreless just used the one that was easiest to implement.
March 16, 2009 at 2:14 pm
Again, curiosity. What was the exact error you received? It may be easily corrected.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply