Get the data from dataset by passing parameters to Stored procedures?

saranrajk — Thu, 26 Dec 2019 13:49:18 GMT

Hi,

I'm trying to get data from the shared dataset by passing parameters and I'm calling stored procedures through the dataset.

When I'm trying the access the data from the dataset using parameters I'm getting the following error,

The value for parameter 'ItemPath' is not specified. It is either missing from the function call, or it is set to null.

The following is my stored procedure is MSSQL

CREATE PROCEDURE TEST_PROCDURE (@ProductName as VARCHAR(100),@BrandName as VARCHAR(100),@StoreName VARCHAR(100),@CityName VARCHAR(100))
AS
BEGIN
if ((@ProductName IS NOT NULL) AND (@BrandName IS NULL) AND (@StoreName IS NULL) AND (@CityName IS NULL))
BEGIN
select PRD.PRODUCT_NAME,sum(PRD.LIST_PRICE)
from BikeStores.production.products PRD
where PRD.PRODUCT_NAME = @ProductName
group by PRD.PRODUCT_NAME
END
else if ((@ProductName IS NULL) AND (@BrandName IS NOT NULL) AND (@StoreName IS NULL) AND (@CityName IS NULL))
begin
select BRND.brand_name,sum(OITM.list_price)
from BikeStores.production.brands BRND
join BikeStores.production.products PRD on BRND.brand_id = PRD.brand_id
join BikeStores.sales.order_items OITM on PRD.product_id = OITM.product_id
where BRND.brand_name = @BrandName
group by BRND.brand_name
end
else if ((@ProductName IS NULL) AND (@BrandName IS NULL) AND (@StoreName IS NOT NULL) AND (@CityName IS NULL))
begin
select STS.store_name,sum(OITM.list_price)
from BikeStores.sales.stores STS
join BikeStores.sales.orders ORD on STS.store_id = ORD.order_id
join BikeStores.sales.order_items OITM on ORD.order_id = OITM.order_id
where STS.store_name = @StoreName
group by STS.store_name
end
else if ((@ProductName IS NULL) AND (@BrandName IS NULL) AND (@StoreName IS NULL) AND (@CityName IS NOT NULL))
begin
select STS.city,sum(OITM.list_price)
from BikeStores.sales.stores STS
join BikeStores.sales.orders ORD on STS.store_id = ORD.order_id
join BikeStores.sales.order_items OITM on ORD.order_id = OITM.order_id
where STS.city = @CityName
group by STS.city
end
END;

Following is the screenshot my shared dataset and parameters.

Following is the screen shot of calling it from postman

topic Get the data from dataset by passing parameters to Stored procedures? in Report Server

Get the data from dataset by passing parameters to Stored procedures?