SQL Server存储过程：表变量参数传递及PowerBI适配问询

Got it, let's break down your two questions step by step since you're working with SQL Server stored procedures in PowerBI where variable declarations aren't allowed.

1. How Table Variables @p3 and @p9 Are Passed as Parameters #

First, let's clarify what's happening here:

• dbo.IdsTableType is a user-defined table type in your SQL Server database. It's likely defined something like this (you can confirm via sp_help 'dbo.IdsTableType'):

  CREATE TYPE dbo.IdsTableType AS TABLE (Id INT NOT NULL);
  

• The stored procedure StatisticView174 has parameters @IdStocks and @IdCompanys that accept this table type (these are table-valued parameters, or TVPs).

In standard SQL Server T-SQL, passing TVPs follows this flow:

1. Declare a table variable using the custom type (like @p3 and @p9).
2. Insert rows into the table variable to populate your data set.
3. Pass the populated table variable as an argument to the stored procedure.

This is the official, supported method for passing multi-row data to a stored procedure via TVPs.

2. Inline Replacement Without Declaring Variables (For PowerBI) #

Since PowerBI doesn't support DECLARE statements, you can replace the table variables directly with table-valued expressions that match the exact structure of dbo.IdsTableType. Here's how to implement this:

Instead of declaring and populating a table variable, use a subquery with the VALUES clause to generate the required table data inline. Just ensure the column names and data types perfectly match your custom table type.

Modified Query Ready for PowerBI #

exec StatisticView174 
  @TimeFrameStart = '2019-01-01 00:00:00', 
  @TimeFrameEnd = '2020-01-01 00:00:00', 
  @IdStocks = (SELECT * FROM (VALUES (1)) AS Temp(Id)), 
  @IdLng = 1, 
  @GuiLng = N'en', 
  @IdUser = 213, 
  @OnlyOwn = 0, 
  @IdAddress = NULL, 
  @IdCompanys = (SELECT * FROM (VALUES (1), (6), (0)) AS Temp(Id)), 
  @IdProject = NULL, 
  @HourMinDiv = 60

Key Notes to Avoid Errors: #

• Replace Id in the subquery with the actual column name from your dbo.IdsTableType if it's different.
• If your custom table type has multiple columns, include all required columns in the VALUES clause. For example, if it had Id INT and Category NVARCHAR(50), you'd use VALUES (1, N'CategoryA'), (2, N'CategoryB').
• Double-check that data types in your inline table match the custom type (e.g., if the type uses BIGINT instead of INT, adjust your values to avoid conversion failures).

内容的提问来源于stack exchange，提问作者Gardinero