none
SQL SELECT query to get results based on parameter

    질문

  • Hi,

    I have tables like this in my stored procedure. 2 parameters clientID and eventID are passed in. I need to determine

    • if client is internal or external type.
    • If internal type, should return all events no matter which client they belong to.
    • If client is external type, then based on event id, details should be returned.
    • If Client is external and event ID is null then, all events for that external client should be returned.
    • If Client is exteranal and eventID is passed, then only that event details can be returned. 

    How can I write this in one single select query:

    Client table

    DECLARE @clients TABLE (

    ClientID SMALLINT,

    ShortName VARCHAR(25),

    ClientType varchar(10))

    Insert into @clients

    Select 1, ‘LockHeedMartin’, ‘External’

    Union

    Select 2, ‘Boeing’, ‘External’

    Union

    Select 3, ‘IBM’, ‘Internal’

    Events table

    DECLARE @events TABLE (

    EventID smallint

    ClientID SMALLINT,

    EventName VARCHAR(25

    )

    Insert into @events

    Select 1, 1, ‘ASU’

    Union

    Select 2, 1, ‘RAM’

    Union

    Select 3, 1, ‘ASF’

    Union

    Select 4, 2, ‘AAA’

    Union

    Select 5, 2, ‘ASDF’

    Union

    Select 6, 3, ‘KLM’

    From Front end for my stored procedure I get parameters ClientID, eventID.

    ClientID is always passed

    EventID can be NULL or event ID

    My stored procedure should do this.

    1.   Find out using clientID that client is ‘External’ or ‘Internal’ Type

    2.   If external type, then check for eventID. If eventID is null, then return all events belonging to that client.

    3.   If External Type and event ID is passed, then get details of only that event.

    4.   If client is Internal type, then return all events no matter which client event belongs to.

    Results expected from SQL query

    If ClientID 3 (Internal client)

        Return all events

    If ClientID = 1 (External Client), Event ID = null

        1, 1, ‘ASU’

        2, 1, ‘RAM’

        3, 1, ‘ASF’

    If ClientID = 1, EventID = 2 then

        2, 1, Ram

    Thank You

    2018년 6월 12일 화요일 오전 4:20

모든 응답

  • CREATE PROC soname

    @clientID int,

    @eventID int

    as

    If @ClientID =3 

        select * from tb join ....

    If @ClientID =3 and @EventID is null

        select <cols> from tb join ....where ClientID =@ClientID  and @EventID is null

    If @ClientID =3 and @EventID=2

        select <cols> from tb join. where ClientID =@ClientID =3 and @EventID =@EventID


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    2018년 6월 12일 화요일 오전 4:36
    답변자
  • Hi Spunny,

    You mean to want this?

    CREATE TABLE  clients
    (
    ClientID  SMALLINT, 
    ShortName  VARCHAR(25),
    ClientType varchar(10)
    )
    
    Insert into clients
    Select 1, 'LockHeedMartin', 'External'
    Union
    Select 2, 'Boeing', 'External'
    Union
    Select 3, 'IBM', 'Internal'
    --
    CREATE TABLE Eventss  
    (
    EventID smallint,
    ClientID  SMALLINT, 
    EventName  VARCHAR(25)
    )
    
    Insert into Eventss 
    Select 1, 1, 'ASU'
    Union 
    Select 2, 1, 'RAM'
    Union
    Select 3, 1, 'ASF'
    Union
    Select 4, 2, 'AAA'
    Union
    Select 5, 2, 'ASDF'
    Union
    Select 6, 3, 'KLM'
    
    
    create proc PassClient
    @ClientID SMALLINT,
    @EventID SMALLINT
    as
    begin
    select T.ClientID,T.EventID,T.EventName 
    from Eventss T 
    LEFT JOIN clients T1 ON T.ClientID=T1.ClientID
    where 3=@ClientID
    OR (T.ClientID=@ClientID AND @ClientID!=3 AND @EventID IS NULL)
    OR (@ClientID!=3 AND T.ClientID=@ClientID AND EventID=@EventID)
    end

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 12일 화요일 오전 5:15
  • you can create a procedure like this

    CREATE PROC GetClientEvents
    @ClientID int,
    @EventID int = NULL
    AS
    select c.*,e.EventID,e.EventName
    from @events e
    join @clients c
    on e.ClientID = c.ClientID
    where c.ClientID = @clientid
    and (EventID = @EventID
    OR @EventID IS NULL
    OR c.ClientType = 'Internal')
    
    GO





    This will list out all events in case of Internal clients and for External it will return only event details of the ID if passed and if default (NULL) it will return all



    see example illustrations below for various of calling it




    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 12일 화요일 오전 5:43
  • Thank You Visakh.

    Exec GetClientEvents 3 Means you are passing ClientID 3, which in internal client. It is supposed to return all events no matter what client it belongs to.

    EXEC GetClientEvents 3 should return

    Events 1 through 6.

    It is not returning that.

    Thank You

    2018년 6월 12일 화요일 오후 3:13
  • Thank You will. I will test and respond.
    2018년 6월 12일 화요일 오후 3:14
  • THanks Uri. I can't specifically put a condition that if @ClientID = 3. There are multiple clients that are internal.

    Thank You

    2018년 6월 12일 화요일 오후 3:15
  • Thank You Visakh.

    Exec GetClientEvents 3 Means you are passing ClientID 3, which in internal client. It is supposed to return all events no matter what client it belongs to.

    EXEC GetClientEvents 3 should return

    Events 1 through 6.

    It is not returning that.

    Thank You

    yes

    what do you mean?

    Events 1 to 6 are not client 3 s events

    in your example only Event 6 belongs to Client which is what it returns

    Are you telling it needs to return ALL events regardless of client id for internal clients? Sorry but that doesnt sound correct to me

    As I understand what you need is this

    All events for a client if its internal or event id is not passed

    And only required event when client is external type and a eventid is passed

    This is exactly what it does now


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 12일 화요일 오후 3:33
  • Try this ,

    Create  TABLE clients( clientid SMALLINT, shortname VARCHAR(25), clienttype 
    VARCHAR(10)) 
    INSERT INTO clients SELECT 1, 'lockheedmartin', 'external' 
    UNION SELECT 2, 'boeing', 'external' UNION SELECT 3, 'ibm', 
    'internal' 
    --events TABLE 
    Create  TABLE eventstable ( eventid SMALLINT ,clientid 
    SMALLINT, eventname VARCHAR(25 ) )
    INSERT INTO eventstable SELECT 1, 1, 'asu' 
    UNION SELECT 2, 1, 'ram' UNION SELECT 3, 1, 'asf' UNION SELECT 4, 2, 
    'aaa' UNION SELECT 5, 2, 'asdf' UNION SELECT 6, 3, 'klm' 
    

    PROC,

    CREATE PROC GetEvents
    @ClientID int,
    @EventID int = NULL
    AS
    if exists (select * from clients where  clientid=@ClientID and clienttype='internal')
    BEGIN 
     
    select * from eventstable e
    END 
    else 
    select e.* from eventstable e 
    inner join clients c 
    on e.clientid=c.clientid 
    where c.clientid= @ClientID and isNULL(@EventID,e.eventid)= e.eventid
    
    GO
    
    exec GetEvents 3,NULL
    go
    exec GetEvents 2,NULL 
    go
    exec GetEvents 2,5 
    go


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 6월 12일 화요일 오후 3:55
  • Yes Visakh. That is what I siad. When Client is 'Internal' Return all events of all clients. That is the requirement. 
    2018년 6월 12일 화요일 오후 5:57
  • Yes Visakh. That is what I siad. When Client is 'Internal' Return all events of all clients. That is the requirement. 

    Hi Spunny,

    What is the progress of your problem? Have you finished testing the solutions above?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 15일 금요일 오전 6:53