I am trying to have multiple Select Statements in my Stored Procedure and I want to call a particular Select Statement only when the value of its parameters IS NOT NULL. Something like this: CREATE PROCEDURE [dbo].[Test] @A BIGINT = NULL, @P VARCHAR(MAX) = NULL, @S INT = NULL, AS --Report 1 If(@A IS NOT NULL AND @P IS NOT NULL) BEGIN SET NOCOUNT ON; Select Col1,Col2,Col3 From Table1 Where Number = @A AND Product = @P --Report 2 If(@A IS NOT NULL AND @P IS NOT NULL AND @S IS NOT NULL) BEGIN SET NOCOUNT ON; Select Col1,Col2,Col3 From Table1 Where Number = @A AND Product = @P AND Col3 = @S So, Instead of writing multiple stored procedures, I just want to have a single stored procedure, that i can use for my reporting services. Is this possible at all? I was thinking, then in my report's dataset, I could just use Exec MyProc (with only relevant parameter)

This will work - kind of. If @A and @P are NOT NULL then the sp will return results from the first query. If @A and @P and @S are NOT NULL the sp will still return results from the first query. You need to make it more nuanced like: IF(@A IS NOT NULL AND @P IS NOT NULL AND @S IS NULL) You need to make sure the sp is set up to deal with all possible combinations of variable values.Bonediggler

How will the stored procedure be called in SSRS dataset?

http://aspnetnova.blogspot.com/2009/02/reporting-services-parameters-stored.html Bonediggler

I mean, will it be something like this for my 1st report: Exec Proc @A, @P, @S = NULL Or --- Exec Proc @A, @P

You already set @S = NULL in the sp so you don't need to do it again when executing. So Exec Proc @A, @P would work. Bonediggler

But @A and @P are also declared as NULL, so how will SSRS determine that it needs to return the report body for the 1st report?

If you declare those variables when running the sp, the null values are overidden. E.g.: Exec Proc 100, BEANBAG The query is: Select Col1,Col2,Col3 From Table1 Where Number = 100 AND Product = BEANBAGBonediggler

Hi SQLCraze. Thanks for your posting. I think you do not need to do anything special in this case. You are already checking inside your stored procedure about the null values. The only thing is you need to check on the body which particular tablix you need to show on the report based on parameter @S is null or not null. I think this will help. Many thanks Syed Qazafi Anjum