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

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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. 

Answer 5

Check the tables to see if there're data inside.

---

Many Thanks & Best Regards, Hua Min

Answer 6

The data is there.

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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