locked
Fetch top 20 records when null variables pass to proc RRS feed

  • Question

  • Have created a one proc as shown in bellow.

    Create proc usp_GetChatHistory
    @ClientId as Int,
    @MemberId as int,
    @FromDate as Datetime,
    @ToDate as DateTime

    as 
    Begin
     SET NOCOUNT ON;

    if @FromDate is null or @ToDate is null  
    begin   
     set @FromDate = CONVERT(date, GETDATE())  
     set @ToDate= CONVERT(date, GETDATE())  
     end ; 

    select * from ChatHistory with (nolock)
    where ClientID=@ClientId
    and MemberId=@MemberId
    and CreatedDate >= @FromDate
    and CreatedDate < DATEADD(dd,1,@ToDate)
    END

    GO


    1. If i pass all null variables, it should fetch top 20 records
    2. If i pass particular variables, i need those data only 

    Could you please help on above proc


    Thanks Bala Narasimha

    Wednesday, December 4, 2019 7:45 AM

Answers

  • Hi,

    Try this:

    alter proc usp_GetChatHistory
    (@ClientId as Int = NULL,
    @MemberId as int = NULL,
    @FromDate  Datetime = NULL,
    @ToDate as DateTime = NULL
    )
    as 
    Begin
     SET NOCOUNT ON;
     if coalesce(@ClientId, @MemberId, @FromDate, @ToDate) is NULL
        select top 20 * from ChatHistory with (nolock)
     else if coalesce(@ClientId, @MemberId) is not null and (@FromDate is null or @ToDate is null)
        select * from ChatHistory with (nolock)
        where ClientID=@ClientId
        and MemberId=@MemberId
        and CreatedDate between getdate() and DATEADD(dd,1,getdate())
    else
        select * from ChatHistory with (nolock)
        where ClientID=@ClientId
        and MemberId=@MemberId
        and CreatedDate >= @FromDate
        and CreatedDate < @ToDate
    END

    GO

    Mark as answer if it works. Thanks.




    • Edited by Soumen Barua Wednesday, December 4, 2019 8:22 AM
    • Marked as answer by BaluChalla Wednesday, December 4, 2019 12:31 PM
    Wednesday, December 4, 2019 8:14 AM

All replies

  • Hi,

    Try this:

    alter proc usp_GetChatHistory
    (@ClientId as Int = NULL,
    @MemberId as int = NULL,
    @FromDate  Datetime = NULL,
    @ToDate as DateTime = NULL
    )
    as 
    Begin
     SET NOCOUNT ON;
     if coalesce(@ClientId, @MemberId, @FromDate, @ToDate) is NULL
        select top 20 * from ChatHistory with (nolock)
     else if coalesce(@ClientId, @MemberId) is not null and (@FromDate is null or @ToDate is null)
        select * from ChatHistory with (nolock)
        where ClientID=@ClientId
        and MemberId=@MemberId
        and CreatedDate between getdate() and DATEADD(dd,1,getdate())
    else
        select * from ChatHistory with (nolock)
        where ClientID=@ClientId
        and MemberId=@MemberId
        and CreatedDate >= @FromDate
        and CreatedDate < @ToDate
    END

    GO

    Mark as answer if it works. Thanks.




    • Edited by Soumen Barua Wednesday, December 4, 2019 8:22 AM
    • Marked as answer by BaluChalla Wednesday, December 4, 2019 12:31 PM
    Wednesday, December 4, 2019 8:14 AM
  • Try this approach too:

       select top(20) . . .

       where ClientID = ISNULL(@ClientId, ClientID)

       and MemberId = ISNULL(@MemberId, MemberId)

       . . .

    or this:

       where (@ClientId IS NULL or ClientID = @ClientId)

       and (@MemberId IS NULL or MemberId = @MemberId)



    • Edited by Viorel_MVP Wednesday, December 4, 2019 8:23 AM
    Wednesday, December 4, 2019 8:20 AM
  • Simple IF ELSE block

    if @ClientId IS NULL
    AND @MemberId IS NULL
    AND FromDate IS NULL
    AND ToDate IS NULL

    BEGIN

    SELECT TOP..

    AND

    ELSE

    BEGIN

    YOUR SELECT

    END


    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

    Wednesday, December 4, 2019 8:32 AM
    Answerer