带有Where子句的表值参数

I'm trying to get something like the below code to work. If I hard code "WHERE ip.ICD9Code IN (SELECT RiskFactorICD9 FROM @RiskFactorICD9 WHERE RiskFactorType = 'Tobacco')" the stored procedure works but I want to also pass @RiskFactorType to look something like "WHERE ip.ICD9Code IN (SELECT RiskFactorICD9 FROM @RiskFactorICD9 WHERE RiskFactorType = @RiskFactorType)". Is this possible?

我想尝试使用下面的代码来工作。如果我硬代码“WHERE ip.ICD9Code IN(SELECT RiskFactorICD9 FROM @ RiskFactorICD9 WHERE RiskFactorType ='Tobacco')”存储过程有效,但我想传递@RiskFactorType看起来像“WHERE ip.ICD9Code IN(SELECT RiskFactorICD9 FROM @ RiskFactorICD9 WHERE RiskFactorType = @RiskFactorType)“。这可能吗?

CREATE TYPE typeRiskFactorsTable AS TABLE 
    (RiskFactorType VARCHAR(30), RiskFactorICD9 VARCHAR(10)) 
GO
DECLARE @RiskFactors typeRiskFactorsTable
GO

CREATE PROCEDURE dflt.GetRiskFactors 
@RiskFactorICD9 typeRiskFactorsTable READONLY, 
@RiskFactorType typeRiskFactorsTable READONLY

AS

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2014-01-01 00:00:00' 
SET @EndDate = '2015-12-31 23:59:59'

SELECT X.* 
INTO [Dflt].[RiskFactorsX]
FROM 
    (SELECT c.ID, ip.AdmitDateTime as DateTime, ip.Sta3n, ip.ICD9Code, ip.ICD9Description, 'IP' as DS
        FROM dbo.Inpat_Inpatient ip
            INNER JOIN dbo.COHORT c ON (c.ID = ip.ID)
            WHERE ip.ICD9Code IN (SELECT RiskFactorICD9 FROM @RiskFactorICD9 WHERE RiskFactorType = @RiskFactorType)
                AND ip.AdmitDateTime between @StartDate and @EndDate  
    UNION ALL
        SELECT c.ID, ov.VisitDateTime as DateTime, ov.Sta3n, ov.ICD9Code, ov.ICD9Description, 'OP' as DS
        FROM dbo.Outpat_Visit ov 
            INNER JOIN dbo.COHORT c ON (c.ID = ov.ID)
            WHERE ov.ICD9Code IN (SELECT RiskFactorICD9 FROM @RiskFactorICD9 WHERE RiskFactorType = @RiskFactorType)
                AND  ov.VisitDateTime between @StartDate and @EndDate  

    ) X
    ORDER BY ID, DateTime, DS
GO

DECLARE @RiskFactors typeRiskFactorsTable
INSERT @RiskFactors(RiskFactorType,RiskFactorICD9) 
VALUES  ('Tobacco', '305.1'), 
    ('Tobacco', '989.84'), 
    ('Tobacco', 'V15.82'),
    ('PVD', '443.9'),
    ('PTSD', '309.81'),
    ('Hypotension', '458.0'),
    ('Hypotension', '458.1'),
    ('Hypotension', '458.2'),

EXEC dflt.GetRiskFactors @RiskFactorType = 'Tobacco'
GO

@Mark T @eug This is the working code... Thanks.

@Mark T @eug这是工作代码...谢谢。

CREATE TYPE typeRiskFactorsTable AS TABLE 
    (RiskFactorType VARCHAR(30), RiskFactorICD9 VARCHAR(10)) 
GO

CREATE PROCEDURE dflt.GetRiskFactors 
@RiskFactorICD9 typeRiskFactorsTable READONLY, 
@RiskFactorType VARCHAR(30)

AS

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2014-01-01 00:00:00' 
SET @EndDate = '2015-12-31 23:59:59'

SELECT X.* 
INTO [Dflt].[RiskFactorsX]
FROM 
    (SELECT c.ID, ip.AdmitDateTime as DateTime, ip.Sta3n, ip.ICD9Code, ip.ICD9Description, 'IP' as DS
        FROM dbo.Inpat_Inpatient ip
            INNER JOIN dbo.COHORT c ON (c.ID = ip.ID)
            WHERE ip.ICD9Code IN (SELECT RiskFactorICD9 FROM @RiskFactorICD9 WHERE RiskFactorType = @RiskFactorType)
                AND ip.AdmitDateTime between @StartDate and @EndDate  
    UNION ALL
        SELECT c.ID, ov.VisitDateTime as DateTime, ov.Sta3n, ov.ICD9Code, ov.ICD9Description, 'OP' as DS
        FROM dbo.Outpat_Visit ov 
            INNER JOIN dbo.COHORT c ON (c.ID = ov.ID)
            WHERE ov.ICD9Code IN (SELECT RiskFactorICD9 FROM @RiskFactorICD9 WHERE RiskFactorType = @RiskFactorType)
                AND  ov.VisitDateTime between @StartDate and @EndDate  

    ) X
    ORDER BY ID, DateTime, DS
GO

DECLARE @RiskFactors typeRiskFactorsTable
INSERT @RiskFactors(RiskFactorType,RiskFactorICD9) 
VALUES  ('Tobacco', '305.1'), 
    ('Tobacco', '989.84'), 
    ('Tobacco', 'V15.82'),
    ('PVD', '443.9'),
    ('PTSD', '309.81'),
    ('Hypotension', '458.0'),
    ('Hypotension', '458.1'),
    ('Hypotension', '458.2'),
EXEC dflt.GetRiskFactors @RiskFactorType = 'Tobacco'

To make the stored procedure GetRiskFactors work for all RiskFactors I would need to pass the created table name to the procedure something like

为了使存储过程GetRiskFactors适用于所有RiskFactors,我需要将创建的表名称传递给类似的过程

SELECT X.* 
INTO @Table

instead of

SELECT X.* 
INTO [Dflt].[RiskFactorsX]

How do I accomplish this.

我该如何做到这一点。

2 个解决方案

#1

The type of @RiskFactorType passed into your stored procedure is typeRiskFactorsTable. I think you want that to actually be a VARCHAR instead.

传递到存储过程的@RiskFactorType类型是typeRiskFactorsTable。我想你希望它实际上是一个VARCHAR。

CREATE PROCEDURE dflt.GetRiskFactors 
    @RiskFactorICD9 typeRiskFactorsTable READONLY, 
    @RiskFactorType VARCHAR(30)
AS

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2014-01-01 00:00:00' 
SET @EndDate = '2015-12-31 23:59:59'

SELECT X.* 
INTO [Dflt].[RiskFactorsX]
FROM 
    (SELECT c.ID, ip.AdmitDateTime as DateTime, ip.Sta3n, ip.ICD9Code, ip.ICD9Description, 'IP' as DS
        FROM dbo.Inpat_Inpatient ip
            INNER JOIN dbo.COHORT c ON (c.ID = ip.ID)
            WHERE ip.ICD9Code IN (SELECT RiskFactorICD9 FROM @RiskFactorICD9 WHERE RiskFactorType = @RiskFactorType)
                AND ip.AdmitDateTime between @StartDate and @EndDate  
    UNION ALL
        SELECT c.ID, ov.VisitDateTime as DateTime, ov.Sta3n, ov.ICD9Code, ov.ICD9Description, 'OP' as DS
        FROM dbo.Outpat_Visit ov 
            INNER JOIN dbo.COHORT c ON (c.ID = ov.ID)
            WHERE ov.ICD9Code IN (SELECT RiskFactorICD9 FROM @RiskFactorICD9 WHERE RiskFactorType = @RiskFactorType)
                AND  ov.VisitDateTime between @StartDate and @EndDate  

    ) X
    ORDER BY ID, DateTime, DS
GO

#2

I see some issues in that code that might be preventing it from working properly.

我看到该代码中的一些问题可能会妨碍它正常工作。

When declaring your procedure you have:

在宣布您的手术时,您有:

CREATE PROCEDURE dflt.GetRiskFactors 
    @RiskFactorICD9 typeRiskFactorsTable READONLY, 
    @RiskFactorType typeRiskFactorsTable READONLY

It looks like @RiskFactorType should be a VARCHAR for your condition to work.

看起来@RiskFactorType应该是VARCHAR,以使您的条件有效。

Also, your procedure has two parameters but you invoke it like this:

此外,您的过程有两个参数,但您可以这样调用它:

EXEC dflt.GetRiskFactors @RiskFactorType = 'Tobacco'

You seem to be missing the first parameter.

你似乎错过了第一个参数。

#1