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