为什么我的存储过程无法执行？

I am having trouble abstracting data using a parameter of table type in my stored procedure below. The data that I am abstracting from consists of over 100 columns/variables. In the stored procedure I focus on four variables: Age_CNT (Age), SEX, ADMSNDT (admission date), and DGNS_CD01 (diagnostic code 1). Variable DGNS_CD01 or diagnostic code 1 has multiple possibilities. For example, in the code below I am looking to abstract records with DGNS_CD01 or diagnostic code value of 390, 391, or 459. Since DGNS_CD01 can have multiple values I declare a variable of type table called @DiseaseCodes and insert values 390, 391, and 459. However, when I execute the stored procedure I find that it does not work with parameter @DiseaseCodes and I cannot figure out why. The other parameters work just fine except @DiseaseCodes

我在下面的存储过程中使用表类型的参数抽象数据时遇到问题。我抽象的数据包含100多个列/变量。在存储过程中，我关注四个变量：Age_CNT（年龄），SEX，ADMSNDT（准入日期）和DGNS_CD01（诊断代码1）。变量DGNS_CD01或诊断代码1具有多种可能性。例如，在下面的代码中，我希望用DGNS_CD01或诊断代码值390,391或459抽象记录。由于DGNS_CD01可以有多个值，我声明一个名为@DiseaseCodes的类型表的变量并插入值390,391，然而，当我执行存储过程时，我发现它不能与参数@DiseaseCodes一起工作，我无法弄清楚原因。除了@DiseaseCodes之外，其他参数都可以正常工作

Below is the query with a small sample of fake data. I use Microsoft SQL Server Management Studio 2014. Any help with the stored procedure would be great! I would love to avoid hard coding it for the specific diagnostic code values because they change based on the request.

下面是一小部分虚假数据的查询。我使用的是Microsoft SQL Server Management Studio 2014.对存储过程的任何帮助都会很棒！我希望避免为特定诊断代码值进行硬编码，因为它们会根据请求进行更改。

  --- This is the name of my database.
    USE [Datasets_For_Researchers]
    GO

  --- Here is fake data to run the query below. 
  CREATE TABLE [dbo].[CMS_Fake_Practice_Data](
[AGE_CNT] [varchar](50) NULL,
[SEX] [varchar](50) NULL,
[ADMSNDT] [varchar](50) NULL,
[DGNS_CD01] [varchar](50) NULL,
) ON [PRIMARY]
GO

INSERT INTO [dbo].[CMS_Fake_Practice_Data] (AGE_CNT, SEX, ADMSNDT, DGNS_CD01)
VALUES (66,1,2000344,390), (66,1,2000355,391),(80,2,2000355,500)
GO


    --- According to online articles, I first must initiate type table that 
    --- will be used to store the diagnostic codes I am using to subset the  
    --- data with. 
    CREATE TYPE list_of_three_character_diagnostic_code_tabletype AS TABLE 
   (DiagnosticCodes int NOT NULL PRIMARY KEY)
    GO


   ---The goal of the stored procedure is to simplify data abstraction. 
   ---The most important parameter are the list of diagnostic codes that are  
   ---passed through parameter @DiseaseCodes of type table. 
  CREATE PROCEDURE [dbo].[RetrieveRecords_CMS_1991_2006_Data]
  (
    @MinimumAge AS INT = NULL
    , @SelectSex AS INT = NULL
    , @SelectYear AS INT = NULL
    , @DiseaseCodes list_of_three_character_diagnostic_code_tabletype 
     READONLY
    )
AS 
  BEGIN 
    SELECT 
      *
    FROM 
      CMS_Fake_Practice_Data
    WHERE 
        (@MinimumAge IS NULL OR AGE_CNT >= @MinimumAge) AND
        (@SelectSex IS NULL OR SEX = @SelectSex) AND 
        (@SelectYear IS NULL OR (SUBSTRING(ADMSNDT, 1,4) = @SelectYear)) AND 
        (CONVERT(INT, (SUBSTRING( DGNS_CD01, 1, 3))) IN (SELECT 
        DiagnosticCodes FROM @DiseaseCodes))

 END
 GO


    --- Then I declare the table variable @DiseaseCodes and insert values 
    --- that are definitely present in the  CMS_Fake_Practice_Data table. 
    DECLARE @DiseaseCodes AS  list_of_three_character_diagnostic_code_tabletype
    INSERT INTO @DiseaseCodes (DiagnosticCodes)
        VALUES (390),(391),(459)
    GO

EDIT I updated my code to reflect the suggestions from others but now receive an error stating that @DiseaseCodes was never declared even though it was in the code above.

编辑我更新了我的代码，以反映其他人的建议，但现在收到一个错误，指出@DiseaseCodes从未声明，即使它在上面的代码中。

    ---I execute the query with the default setting but retrieve no records. 
    EXEC RetrieveRecords_CMS_1991_2006_Data @DiseaseCodes
    GO

2 个解决方案

#1

You are getting no results because you have

你没有得到任何结果

 AND 
        (CONVERT(INT, (SUBSTRING( DGNS_CD01, 1, 3))) IN (SELECT 
        DiagnosticCodes FROM @DiseaseCodes))

So if you execute the procedure using defaults, then @DiseaseCodes is empty, and nothing is IN the empty table, so you get no results. The AND makes this condition mandatory.

因此，如果使用默认值执行该过程，则@DiseaseCodes为空，并且空表中没有任何内容，因此您不会得到任何结果。 AND使此条件成为强制性的。

EDIT:

编辑：

Your latest error is because after you DECLARE and INSERT the @DiseaseCodes table, you have a GO. That ends the batch and destroys the variable. Then your EXEC is in a new batch where the variable has not been declared.

您的最新错误是因为在您DECLARE并插入@DiseaseCodes表后，您有一个GO。结束批处理并销毁变量。那么你的EXEC是一个新的批处理，其中变量尚未声明。

Take out the GO after the DECLARE/INSERT, and change your EXEC to this:

在DECLARE / INSERT之后取出GO，并将EXEC更改为：

EXEC RetrieveRecords_CMS_1991_2006_Data @DiseaseCodes=@DiseaseCodes;

#2

The only things that jump right out... You cant have a batch separator (GO) between the setting of the TVP and the EXEC Proc, and you have to add the TVP to the proc execution.

跳出来的唯一东西......你不能在TVP的设置和EXEC Proc之间有一个批处理分隔符（GO），你必须将TVP添加到proc执行。

So change this...

所以改变这个......

--- Then I declare the table variable @DiseaseCodes and insert values 
--- that are definitely present in the  CMS_Fake_Practice_Data table. 
DECLARE @DiseaseCodes AS  list_of_three_character_diagnostic_code_tabletype
INSERT INTO @DiseaseCodes (DiagnosticCodes)
    VALUES (390),(391),(459)
GO


---I execute the query with the default setting but retrieve no records. 
EXEC RetrieveRecords_CMS_1991_2006_Data 
GO

... to this...

......对......

--- Then I declare the table variable @DiseaseCodes and insert values 
--- that are definitely present in the  CMS_Fake_Practice_Data table. 
DECLARE @DiseaseCodes AS  list_of_three_character_diagnostic_code_tabletype
INSERT INTO @DiseaseCodes (DiagnosticCodes)
    VALUES (390),(391),(459);

---I execute the query with the default setting but retrieve no records. 
EXEC RetrieveRecords_CMS_1991_2006_Data @DiseaseCodes;
GO

#1