locked
How to get all the values from database when no parameter is provided as input? RRS feed

  • Question

  • Hi,

    I am trying to get all the values from the database when no parameter is provided as input.

    I have a query having two parameters @AccountId and @key.

    CREATE PROCEDURE [dbo].[GetDenyList]
    @AccountId nvarchar(100),
    @key nvarchar(400)
    AS
    select New_AccountId AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date'
      from abc.dbo.New_keydenylist 
     where New_AccountId is not null
       AND @AccountId = New_AccountId
     union all
    select new_key AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' 
      from abc.dbo.New_keydenylist 
     where New_key is not null
       and @Key=New_key
    I am able to fetch the values when i provide the parameters but i am not able to get all the values when no parameters is selected in reports.
    Tuesday, September 18, 2012 4:27 AM

Answers

  • Hello Prashant,

    You can test it in SSMS with this query; it's the same as your SQL with the parameter declared as variable:

    DECLARE @AccountId nvarchar(100)
    DECLARE @key nvarchar(400)
    
    
    select New_AccountId AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date'
      from abc.dbo.New_keydenylist 
     where New_AccountId is not null
       AND (@AccountId = New_AccountId OR @AccountId IS NULL)
     union all
    select new_key AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' 
      from abc.dbo.New_keydenylist 
     where New_key is not null
       and (@Key=New_key OR @Key IS NULL)


    Olaf Helper

    Blog Xing

    • Marked as answer by Iric Wen Wednesday, September 26, 2012 9:36 AM
    Tuesday, September 18, 2012 5:45 AM
  • The data is there.

    Try this

    CREATE PROCEDURE [dbo].[GetDenyList] @AccountId nvarchar(100) = NULL, @key nvarchar(400) = NULL AS select New_AccountId AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' from abc.dbo.New_keydenylist where New_AccountId = case when @AccountId is null then New_AccountId else @AccountId end union all select new_key AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' from abc.dbo.New_keydenylist where New_key = case when @Key is null then New_key else @Key end

    GO

    EXEC [GetDenyList]

    Also allowed NULL value for SSRS parameter.

    Thanks, Sachin Surve


    Tuesday, September 18, 2012 5:51 AM

All replies

  • Hello,

    When the parameters are not passed to the SP, then they are NULL valued; so you can filter with IS NULL to threar them as optional (see bold part):

    CREATE PROCEDURE [dbo].[GetDenyList]
    @AccountId nvarchar(100),
    @key nvarchar(400)
    AS
    select New_AccountId AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date'
      from abc.dbo.New_keydenylist 
     where New_AccountId is not null
       AND (@AccountId = New_AccountId OR @AccountId IS NULL)
     union all
    select new_key AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' 
      from abc.dbo.New_keydenylist 
     where New_key is not null
       and (@Key=New_key OR @Key IS NULL)


    Olaf Helper

    Blog Xing

    • Proposed as answer by S_Surve Tuesday, September 18, 2012 4:58 AM
    Tuesday, September 18, 2012 4:35 AM
  • Try

    CREATE PROCEDURE [dbo].[GetDenyList]
    @AccountId nvarchar(100),
    @key nvarchar(400)
    AS
    select New_AccountId AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date'
      from abc.dbo.New_keydenylist 
     where New_AccountId is not null
       AND (@AccountId = New_AccountId or @AccountId is null)
     union all
    select new_key AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' 
      from abc.dbo.New_keydenylist 
     where New_key is not null
       and (@Key=New_key or @Key is null)


    Many Thanks & Best Regards, Hua Min

    Tuesday, September 18, 2012 4:39 AM
  • Try This

    CREATE PROCEDURE [dbo].[GetDenyList]
    @AccountId nvarchar(100),
    @key nvarchar(400)
    AS
    select New_AccountId AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date'
      from abc.dbo.New_keydenylist 
     where New_AccountId = case when @AccountId is null then New_AccountId  else @AccountId  end
     union all
    select new_key AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' 
      from abc.dbo.New_keydenylist 
     where New_key = case when @Key is null then New_key else @Key end

    OR. (Not tested)

    CREATE PROCEDURE [dbo].[GetDenyList]
    @AccountId nvarchar(100),
    @key nvarchar(400)
    AS
    select New_AccountId AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date'
      from abc.dbo.New_keydenylist 
     where New_AccountId = ISNULL(@AccountId, New_AccountId  )
     union all
    select new_key AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' 
      from abc.dbo.New_keydenylist 
     where New_key = ISNULL(@Key ,New_key)


    Thanks, Sachin Surve


    • Edited by S_Surve Tuesday, September 18, 2012 5:08 AM
    Tuesday, September 18, 2012 5:00 AM
  • Hi Olaf Helper,

    I am using this SP for the SSRS report. When i click the view report on the the ssrs report, then i do not get any data on the report.

    I am using the same code as you have provided. Please tell me if i need to make some changes in the procedure. 

    Tuesday, September 18, 2012 5:27 AM
  • Check the tables to see if there're data inside.

    Many Thanks & Best Regards, Hua Min

    Tuesday, September 18, 2012 5:33 AM
  • The data is there.
    Tuesday, September 18, 2012 5:38 AM
  • If you pass null for the parameters, our advice should work for the case. Show how you run the SP, please.

    Many Thanks & Best Regards, Hua Min

    Tuesday, September 18, 2012 5:41 AM
  • Hello Prashant,

    You can test it in SSMS with this query; it's the same as your SQL with the parameter declared as variable:

    DECLARE @AccountId nvarchar(100)
    DECLARE @key nvarchar(400)
    
    
    select New_AccountId AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date'
      from abc.dbo.New_keydenylist 
     where New_AccountId is not null
       AND (@AccountId = New_AccountId OR @AccountId IS NULL)
     union all
    select new_key AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' 
      from abc.dbo.New_keydenylist 
     where New_key is not null
       and (@Key=New_key OR @Key IS NULL)


    Olaf Helper

    Blog Xing

    • Marked as answer by Iric Wen Wednesday, September 26, 2012 9:36 AM
    Tuesday, September 18, 2012 5:45 AM
  • The data is there.

    Try this

    CREATE PROCEDURE [dbo].[GetDenyList] @AccountId nvarchar(100) = NULL, @key nvarchar(400) = NULL AS select New_AccountId AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' from abc.dbo.New_keydenylist where New_AccountId = case when @AccountId is null then New_AccountId else @AccountId end union all select new_key AS 'AccountId/Key', New_Description AS 'Reason',CreatedOn AS 'Date' from abc.dbo.New_keydenylist where New_key = case when @Key is null then New_key else @Key end

    GO

    EXEC [GetDenyList]

    Also allowed NULL value for SSRS parameter.

    Thanks, Sachin Surve


    Tuesday, September 18, 2012 5:51 AM
  • Test the proc from SSMS query window and verify that it behaves as you want. If it does, I suggest you post to an Reportin Services forum, since it then is a matter for how to get RS to generate the proc call you expect. You can always trace the proc call from RS using Profiler, of course, to have some more insight into what is happening.

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, September 18, 2012 6:30 AM