filter some results out
-
Alright, I have a procedure that gives me events associated with specific user:
CREATE PROCEDURE dbo.blablalba
(
@AssignedSP varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(600)
SET @sql =
'Select distinct
Events.EventID,
Events.EventName,
Events.EventType
From Events
Inner Join EventMembers On EventMembers.EventID = Events.EventID
Where EventMembers.UserID IN (' + @AssignedSP + ')'
EXEC(@SQL)
END
RETURN
GO
Every event associated with a FieldID in another table let's say ATABLE. And ATABLE also has row called SOLUTION that has some solutions for that FieldID.
So how can I change above procedure so, that it will not pull out any events unless every EventID has at least one SOLUTION(which always there, if empty - not in table)
-
Your Description is a little confusing.
Can you post some example table definitions, Some sample data, and what you expect the results to look like.
Just guessing here, Try this.
CREATE PROCEDURE dbo.blablalba
(
@AssignedSP varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(600)
SET @sql =
'Select distinct
Events.EventID,
Events.EventName,
Events.EventType
From Events
Inner Join EventMembers On EventMembers.EventID = Events.EventID
Where EventMembers.UserID IN (' + @AssignedSP + ')
and exists (select *
from ATABLE
where Events.FieldID = ATABLE.FieldID)'
EXEC(@SQL)
END
RETURN
GO
Ray
-
Just a guess :
IF Object_id('fnSplit_Set') > 0
DROP FUNCTION dbo.fnSplit_Set
GO
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
DECLARE @Ids as varchar(8000)
SET @IDS = ''
Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds
SET @IDS = left(@IDS, ABS(len(@IDS) - 1))
PRINT @IDS
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank
--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply