How to transfer all values ​​in a table to ssrs when there is no value in the parameters?

Question

I pass two parameters ie @AccountId and @key . I want to show all the values ​​present in the database for @AccountId and @key when there are no values ​​in the parameters ie @AccountId and @key . I want to do this for the same dataset. Is this possible, or have I created different data sets?

I am using the following query:

 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 

Answer 1

If I understand your question well, you should use any predicate that always evaluates to true, regardless of the parameter values ​​passed to the WHERE . For example WHERE 1 = 1 :

  WHERE 1 = 1 AND (@AccountId IS NOT NULL OR AccountId = @AccountId) 

Same thing with the @key parameter:

  WHERE 1 = 1 AND (@key IS NOT NULL OR New_key = @key) 

If the two parameters @key and @AccountId are NULL or one of them, then the WHERE remains valid and returns the entire string as it was not presented.

Answer 2

Check null or parameter equivalence:

 AND ((@AccountId IS NULL) OR (@AccountId = New_AccountId))