[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

stored procedure that will take in optional parameters from ssrs report

hello,

i am starting to write a stored procedure that will take the following input parameters:

current order number - required
prior order number - required
current item number 1 - required
prior item number 1 - required
current item number 2 - optional
prior item number 2 - optional
current item number 3 - optional
prior item number 3 - optional
current item number 4 - optional
prior item number 4 - optional
current item number 5 - optional
prior item number 5 - optional

what would be a good way to handle the optional parameters that will come from the user input to the ssrs report?


so far what i came up with is to create a stored procedure that takes in all the parameters mentioned above:
CREATE PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
	@CustomerNumber_BillTo		NVARCHAR(20)
,	@CurrentSalesQuoteNumber	NVARCHAR(20)
,	@PriorSalesQuoteNumber		NVARCHAR(20)
,	@CurrentItemNumber1			NVARCHAR(20)
,	@PriorItemNumber1			NVARCHAR(20)
,	@CurrentItemNumber2			NVARCHAR(20)
,	@PriorItemNumber2			NVARCHAR(20)
,	@CurrentItemNumber3			NVARCHAR(20)
,	@PriorItemNumber3			NVARCHAR(20)
,	@CurrentItemNumber4			NVARCHAR(20)
,	@PriorItemNumber4			NVARCHAR(20)
,	@CurrentItemNumber5			NVARCHAR(20)
,	@PriorItemNumber5			NVARCHAR(20)
)

Open in new window


but i am able to figure out so far the code when the user is using only the required fields. not sure how to handle the optional parameters.

any ideas, or suggestions are welcome.

thank you much.
0
metropia
Asked:
metropia
  • 6
  • 4
1 Solution
 
Scott PletcherSenior DBACommented:
Providing a default value makes the parameter optional:

CREATE PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
      @CustomerNumber_BillTo            NVARCHAR(20)
,      @CurrentSalesQuoteNumber      NVARCHAR(20)
,      @PriorSalesQuoteNumber            NVARCHAR(20)
,      @CurrentItemNumber1                  NVARCHAR(20)
,      @PriorItemNumber1                  NVARCHAR(20)
,      @CurrentItemNumber2                  NVARCHAR(20) = NULL
,      @PriorItemNumber2                  NVARCHAR(20) = NULL
,      @CurrentItemNumber3                  NVARCHAR(20) = NULL
,      @PriorItemNumber3                  NVARCHAR(20) = NULL
,      @CurrentItemNumber4                  NVARCHAR(20) = NULL
,      @PriorItemNumber4                  NVARCHAR(20) = NULL
,      @CurrentItemNumber5                  NVARCHAR(20) = NULL
,      @PriorItemNumber5                  NVARCHAR(20) = NULL
)
0
 
Nico BontenbalCommented:
When you set the "Allow null value" property of a parameter in SSRS, this parameter becomes optional. In the query you can then do something like this:
where
    field1 = @field1
    and (field2 = @field2 or @field2 is null)

Open in new window

(Field2 is the optional parameter).
See the sample report. You need to change the data source for this report to test it. Enter a,b,c or d as values for the parameters when running the report. A values for the field1 parameter is required, a value for the field2 parameter is optional.
Optional.rdl
0
 
metropiaAuthor Commented:
In the end I will have up to 5 current and prior items (parameters) with item 1 being the only one that is required and the rest optional.

I created my stored procedure using variable tables that are filled if there is a value in the item 2, 3, 4, 5 parameters. That is working in regards to the number of records that are being returned, When I run the stored procedure as it is right now, I get two dataset in return (so far I only coded item 1 and 2)

My question is, how can I have the two data sets be returned as one, but taking into consideration that item 2, 3, 4 ,5 are optional? Can a union all be used within an IF?

I am attaching a copy of my code. I hope is clear and simple enough (although may be redundant) for someone to offer an advice on how to proceed.

Thank you very much.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
metropiaAuthor Commented:
0
 
Nico BontenbalCommented:
No you can't use IF within a UNION. But since you fill the @PriorQuote_PriorItem2 etc. tables only if there is a value for the parameter you can just join all the tables always in one select statement at the end. Or am I missing something. I'm willing to help with the stored procedure but then you need to simplify it. Since I don't have your table structures (let alone data) it's difficult for my to read the stored procedure.
If you can create a sample stored procedure that uses a temp table (or a table declare) at the start and fill this table with some test data, then I can run the stored procedure on my database also. Something like this:
create PROCEDURE Test
(
	@field1	VARCHAR(1),
	@field2 VARCHAR(1)
)
as
begin
    --create temp table with some data
    create table #tmp (field1 varchar(1), field2 varchar(1))
    insert into #tmp (field1,field2) values ('a','b')
    insert into #tmp (field1,field2) values ('a','b')
    insert into #tmp (field1,field2) values ('b','c')
    insert into #tmp (field1,field2) values ('d','b')
    insert into #tmp (field1,field2) values ('c','b')
    insert into #tmp (field1,field2) values ('a','b')
    insert into #tmp (field1,field2) values ('b','b')
    insert into #tmp (field1,field2) values ('b','a')
    insert into #tmp (field1,field2) values ('d','b')
    insert into #tmp (field1,field2) values ('b','a')
    insert into #tmp (field1,field2) values ('d','c')
    insert into #tmp (field1,field2) values ('d','c')
    insert into #tmp (field1,field2) values ('b','a')
    insert into #tmp (field1,field2) values ('d','c')
    insert into #tmp (field1,field2) values ('d','b')
    insert into #tmp (field1,field2) values ('a','d')
    insert into #tmp (field1,field2) values ('b','b')
    insert into #tmp (field1,field2) values ('c','a')
    insert into #tmp (field1,field2) values ('a','d')
    insert into #tmp (field1,field2) values ('b','d')

    --the actual query
    select 
        field1,
        field2
    from 
        #tmp
    where
        field1 = @field1
        or (field1 = @field2 and @field2 is not null)
    order by field1, field2
    --drop temp table
    drop table #tmp
end
go

exec Test 'b', 'c'
exec Test 'a', null

Open in new window

This procedure is independent of the tables and data in the database and makes it easier for us to talk about. Also I hope this example is closer to what you need. As you can see the parameter @field2 is optional. When I use 'a', null as parameters it returns only the 'a' records. But when I use 'a', 'c' as parameters it return the 'a' and the 'c' records. There is no need for table declares to accomplish this. Just a bit more complex where statement.
0
 
metropiaAuthor Commented:
Hi Nicobo.

I will give you the data. Just a few minutes.
0
 
metropiaAuthor Commented:
0
 
metropiaAuthor Commented:
i added the table create and insert into scripts
0
 
metropiaAuthor Commented:
i need to be able to group by each item

like

current sales quote                 current item 1             current requesteddeliverydate
prior sales quote                     prior item 1                  priorrequesteddeliverydate

current sales quote                 current item 2             current requesteddeliverydate
prior sales quote                     prior item 2                  priorrequesteddeliverydate

current sales quote                 current item 3             current requesteddeliverydate
prior sales quote                     prior item 3                  priorrequesteddeliverydate

and so on (up to 5)
0
 
Nico BontenbalCommented:
Sorry, but this site is about me answering your question and not about me doing your work. I've updated my example so it does what I think you need. I've got multi select parameters, one required and the other two optional. And the data is grouped for the selections of each parameter. If this sample differs from what you need please let me know what the differences are and I'll update my sample.
Optional.rdl
0
 
Nico BontenbalCommented:
Just out of curiosity: If the first answer given by ScottPletcher was the rigth solution for you, why did you keep asking follow up questions?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now