Parameter or variable has an invalid data type

Question

Hello,

Spent few hours searching for a solution but totally stuck building the procedure and getting the error:

"Msg 2715, Level 16, State 3, Procedure sp_merge_Clv_ProductsAll, Line 3 [Batch Start Line 0]
Column, parameter, or variable #1: Cannot find data type BSR_DWH_P.Clv_ProductsAll_v.
Parameter or variable '@Anonymous _ProductsAll' has an invalid data type.
Msg 1087, Level 16, State 1, Procedure sp_merge_Clv_ProductsAll, Line 9 [Batch Start Line 0]
Must declare the table variable "@Anonymous _ProductsAll".

Hope someone can shed some light how to fix it.
Thanks in advance

CREATE PROCEDURE BSR_DWH_P.sp_merge_Clv_ProductsAll  
  
@Clv_ProductsAll BSR_DWH_P.Clv_ProductsAll_v  READONLY  
  
AS  
BEGIN    
  
  MERGE BSR_DWH_P.Clv_ProductsAll AS dwh  
  USING @Clv_ProductsAll AS stg  
  ON (dwh.id = stg.id)  
  WHEN MATCHED THEN  
      UPDATE SET   
	"id" = stg.id  
	,"date" = stg."date"  
	,region = stg.region  
	,availability_availability_compliant = stg.availability_availability_compliant  
	,availability_status = stg.availability_status  
	,brand = stg.brand  
	,category = stg.category  
	,content_content_compliant = stg.content_content_compliant  
	,content_milestone1_compliant = stg.content_milestone1_compliant  
	,content_milestone2_compliant = stg.content_milestone2_compliant  
	,content_milestone3_compliant = stg.content_milestone3_compliant  
	,content_milestone4_compliant = stg.content_milestone4_compliant  
	,content_milestone5_compliant = stg.content_milestone5_compliant  
	,content_milestone6_compliant = stg.content_milestone6_compliant  
	,content_milestone7_compliant = stg.content_milestone7_compliant  
	,content_milestone8_compliant = stg.content_milestone8_compliant  
	,content_milestone9_compliant = stg.content_milestone9_compliant  
	,content_milestone10_compliant = stg.content_milestone10_compliant  
	,dimension1 = stg.dimension1  
	,dimension2 = stg.dimension2  
	,dimension3 = stg.dimension3  
	,dimension4 = stg.dimension4  
	,dimension5 = stg.dimension5  
	,dimension6 = stg.dimension6  
	,dimension7 = stg.dimension7  
	,dimension8 = stg.dimension8  
	,harvested_duplicate_image_count = stg.harvested_duplicate_image_count  
	,harvested_image_count = stg.harvested_image_count  
	,harvested_product_image_url = stg.harvested_product_image_url  
	,harvested_url = stg.harvested_url  
	,harvested_video_count = stg.harvested_video_count  
	,imaging_imaging_compliant = stg.imaging_imaging_compliant  
	,imaging_status = stg.imaging_status  
	,is_competitor = stg.is_competitor  
	,manufacturer = stg.manufacturer  
	,menu_menu_compliant = stg.menu_menu_compliant  
	,menu_position = stg.menu_position  
	,online_store = stg.online_store  
	,pricing_currency_symbol = stg.pricing_currency_symbol  
	,pricing_diff_from_min_max = stg.pricing_diff_from_min_max  
	,pricing_diff_from_min_max_percent = stg.pricing_diff_from_min_max_percent  
	,pricing_diff_from_msrp = stg.pricing_diff_from_msrp  
	,pricing_diff_from_msrp_compliant = stg.pricing_diff_from_msrp_compliant  
	,pricing_diff_from_msrp_percent = stg.pricing_diff_from_msrp_percent  
	,pricing_diff_from_previous_observed_price = stg.pricing_diff_from_previous_observed_price  
	,pricing_diff_from_previous_observed_price_percent = stg.pricing_diff_from_previous_observed_price_percent  
	,pricing_max_price = stg.pricing_max_price  
	,pricing_min_price = stg.pricing_min_price  
	,pricing_msrp = stg.pricing_msrp  
	,pricing_net_diff_from_min = stg.pricing_net_diff_from_min  
	,pricing_net_diff_from_min_compliant = stg.pricing_net_diff_from_min_compliant  
	,pricing_net_diff_from_min_percent = stg.pricing_net_diff_from_min_percent  
	,pricing_observed_price = stg.pricing_observed_price  
	,pricing_previous_observed_price = stg.pricing_previous_observed_price  
	,pricing_price_compliant = stg.pricing_price_compliant  
	,promotion_is_on_promotion = stg.promotion_is_on_promotion  
	,promotion_items = stg.promotion_items  
	,ratings_and_reviews_overall_rating = stg.ratings_and_reviews_overall_rating  
	,ratings_and_reviews_rating_compliant = stg.ratings_and_reviews_rating_compliant  
	,ratings_and_reviews_review_compliant = stg.ratings_and_reviews_review_compliant  
	,ratings_and_reviews_review_count = stg.ratings_and_reviews_review_count  
	,sales_average_sales = stg.sales_average_sales  
	,sales_currency_symbol = stg.sales_currency_symbol  
	,search_scores_search_score_compliant = stg.search_scores_search_score_compliant  
	,traffic_page_views = stg.traffic_page_views  
	,traffic_rank = stg.traffic_rank  
	,trusted_mpc = stg.trusted_mpc  
	,trusted_product_description = stg.trusted_product_description  
	,trusted_rpc = stg.trusted_rpc  
	,trusted_upc = stg.trusted_upc	  
  
  WHEN NOT MATCHED THEN  
      INSERT (  
	id  
	,date  
	,region  
	,availability_availability_compliant  
	,availability_status  
	,brand  
	,category  
	,content_content_compliant  
	,content_milestone1_compliant  
	,content_milestone2_compliant  
	,content_milestone3_compliant  
	,content_milestone4_compliant  
	,content_milestone5_compliant  
	,content_milestone6_compliant  
	,content_milestone7_compliant  
	,content_milestone8_compliant  
	,content_milestone9_compliant  
	,content_milestone10_compliant  
	,dimension1  
	,dimension2  
	,dimension3  
	,dimension4  
	,dimension5  
	,dimension6  
	,dimension7  
	,dimension8  
	,harvested_duplicate_image_count  
	,harvested_image_count  
	,harvested_product_image_url  
	,harvested_url  
	,harvested_video_count  
	,imaging_imaging_compliant  
	,imaging_status  
	,is_competitor  
	,manufacturer  
	,menu_menu_compliant  
	,menu_position  
	,online_store  
	,pricing_currency_symbol  
	,pricing_diff_from_min_max  
	,pricing_diff_from_min_max_percent  
	,pricing_diff_from_msrp  
	,pricing_diff_from_msrp_compliant  
	,pricing_diff_from_msrp_percent  
	,pricing_diff_from_previous_observed_price  
	,pricing_diff_from_previous_observed_price_percent  
	,pricing_max_price  
	,pricing_min_price  
	,pricing_msrp  
	,pricing_net_diff_from_min  
	,pricing_net_diff_from_min_compliant  
	,pricing_net_diff_from_min_percent  
	,pricing_observed_price  
	,pricing_previous_observed_price  
	,pricing_price_compliant  
	,promotion_is_on_promotion  
	,promotion_items  
	,ratings_and_reviews_overall_rating  
	,ratings_and_reviews_rating_compliant  
	,ratings_and_reviews_review_compliant  
	,ratings_and_reviews_review_count  
	,sales_average_sales  
	,sales_currency_symbol  
	,search_scores_search_score_compliant  
	,traffic_page_views  
	,traffic_rank  
	,trusted_mpc  
	,trusted_product_description  
	,trusted_rpc  
	,trusted_upc  
        )  
      VALUES (  
	stg.id  
	,stg.date  
	,stg.region  
	,stg.availability_availability_compliant  
	,stg.availability_status  
	,stg.brand  
	,stg.category  
	,stg.content_content_compliant  
	,stg.content_milestone1_compliant  
	,stg.content_milestone2_compliant  
	,stg.content_milestone3_compliant  
	,stg.content_milestone4_compliant  
	,stg.content_milestone5_compliant  
	,stg.content_milestone6_compliant  
	,stg.content_milestone7_compliant  
	,stg.content_milestone8_compliant  
	,stg.content_milestone9_compliant  
	,stg.content_milestone10_compliant  
	,stg.dimension1  
	,stg.dimension2  
	,stg.dimension3  
	,stg.dimension4  
	,stg.dimension5  
	,stg.dimension6  
	,stg.dimension7  
	,stg.dimension8  
	,stg.harvested_duplicate_image_count  
	,stg.harvested_image_count  
	,stg.harvested_product_image_url  
	,stg.harvested_url  
	,stg.harvested_video_count  
	,stg.imaging_imaging_compliant  
	,stg.imaging_status  
	,stg.is_competitor  
	,stg.manufacturer  
	,stg.menu_menu_compliant  
	,stg.menu_position  
	,stg.online_store  
	,stg.pricing_currency_symbol  
	,stg.pricing_diff_from_min_max  
	,stg.pricing_diff_from_min_max_percent  
	,stg.pricing_diff_from_msrp  
	,stg.pricing_diff_from_msrp_compliant  
	,stg.pricing_diff_from_msrp_percent  
	,stg.pricing_diff_from_previous_observed_price  
	,stg.pricing_diff_from_previous_observed_price_percent  
	,stg.pricing_max_price  
	,stg.pricing_min_price  
	,stg.pricing_msrp  
	,stg.pricing_net_diff_from_min  
	,stg.pricing_net_diff_from_min_compliant  
	,stg.pricing_net_diff_from_min_percent  
	,stg.pricing_observed_price  
	,stg.pricing_previous_observed_price  
	,stg.pricing_price_compliant  
	,stg.promotion_is_on_promotion  
	,stg.promotion_items  
	,stg.ratings_and_reviews_overall_rating  
	,stg.ratings_and_reviews_rating_compliant  
	,stg.ratings_and_reviews_review_compliant  
	,stg.ratings_and_reviews_review_count  
	,stg.sales_average_sales  
	,stg.sales_currency_symbol  
	,stg.search_scores_search_score_compliant  
	,stg.traffic_page_views  
	,stg.traffic_rank  
	,stg.trusted_mpc  
	,stg.trusted_product_description  
	,stg.trusted_rpc  
	,stg.trusted_upc	  
        );  
END  

Answer 1

Hi @BartoszWachocki-4076，

According to your error message and create procedure statement, you may need to create a user-defined table type for Clv_ProductsAll_v.

You could ask someone who shared this create procedure statement with you or create this table type by yourself according to your requirement.

Please refer below example or below link for more details about how to create a user-defined table type.

CREATE TYPE Clv_ProductsAll_v AS TABLE     
    ( id int,    
    [date] date,  
 region varchar(100),  
 availability_availability_compliant  varchar(100),  
 availability_status varchar(100),  
 brand  varchar(100),  
 category  varchar(100),  
 content_content_compliant varchar(100)  
     ,content_milestone1_compliant varchar(100)  
     ,content_milestone2_compliant varchar(100)  
     ,content_milestone3_compliant varchar(100)  
     ,content_milestone4_compliant varchar(100)  
     ,content_milestone5_compliant varchar(100)  
     ,content_milestone6_compliant varchar(100)  
     ,content_milestone7_compliant varchar(100)  
     ,content_milestone8_compliant varchar(100)  
     ,content_milestone9_compliant  varchar(100)  
     ,content_milestone10_compliant varchar(100)  
     ,dimension1 varchar(100)  
     ,dimension2 varchar(100)  
     ,dimension3 varchar(100)  
     ,dimension4 varchar(100)  
     ,dimension5 varchar(100)  
     ,dimension6 varchar(100)  
     ,dimension7 varchar(100)  
     ,dimension8 varchar(100)  
     ,harvested_duplicate_image_count varchar(100)  
     ,harvested_image_count varchar(100)  
     ,harvested_product_image_url varchar(100)  
     ,harvested_url varchar(100)  
     ,harvested_video_count varchar(100)  
     ,imaging_imaging_compliant varchar(100)  
     ,imaging_status varchar(100)  
     ,is_competitor varchar(100)  
     ,manufacturer varchar(100)  
     ,menu_menu_compliant varchar(100)  
     ,menu_position varchar(100)  
     ,online_store varchar(100)  
     ,pricing_currency_symbol varchar(100)  
     ,pricing_diff_from_min_max varchar(100)  
     ,pricing_diff_from_min_max_percent varchar(100)  
     ,pricing_diff_from_msrp varchar(100)  
     ,pricing_diff_from_msrp_compliant varchar(100)  
     ,pricing_diff_from_msrp_percent varchar(100)  
     ,pricing_diff_from_previous_observed_price varchar(100)  
     ,pricing_diff_from_previous_observed_price_percent varchar(100)  
     ,pricing_max_price varchar(100)  
     ,pricing_min_price varchar(100)  
     ,pricing_msrp varchar(100)  
     ,pricing_net_diff_from_min varchar(100)  
     ,pricing_net_diff_from_min_compliant varchar(100)  
     ,pricing_net_diff_from_min_percent varchar(100)  
     ,pricing_observed_price varchar(100)  
     ,pricing_previous_observed_price varchar(100)  
     ,pricing_price_compliant varchar(100)  
     ,promotion_is_on_promotion varchar(100)  
     ,promotion_items varchar(100)  
     ,ratings_and_reviews_overall_rating varchar(100)  
     ,ratings_and_reviews_rating_compliant varchar(100)  
     ,ratings_and_reviews_review_compliant varchar(100)  
     ,ratings_and_reviews_review_count varchar(100)  
     ,sales_average_sales varchar(100)  
     ,sales_currency_symbol varchar(100)  
     ,search_scores_search_score_compliant varchar(100)  
     ,traffic_page_views varchar(100)  
     ,traffic_rank varchar(100)  
     ,trusted_mpc varchar(100)  
     ,trusted_product_description varchar(100)  
     ,trusted_rpc varchar(100)  
     ,trusted_upc varchar(100)  
 );    
GO    

Creating a user-defined table type

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer 2

It looks like you need to create the user defined table type BSR_DWH_P.Clv_ProductsAll_v.

Answer 3

Thanks @MelissaMa-MSFT
That's what I missed and solved my isssue.
:)