Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
saranrajk
Helper II
Helper II

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

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.
Stored procedureStored procedure
Parameters of stored procedureParameters of stored procedure
 
Following is the screen shot of calling it from postman
calling it from postmancalling it from postman
 
 
0 REPLIES 0

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.