T-SQL View Subquery Where Clause

Question

I'm looking to see if this is even possible. I am constructing a view that needs to have a sub query. However, the sub query needs to have the same where clause as the main SQL statement.

I need to have this in a view, because I have to do a JOIN from another table, and a stored procedure, nor a table UDF will work in my case.

I've constructed the following example to show what I'm trying to do:

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL BEGIN DROP TABLE #TableA END
IF OBJECT_ID('tempdb..#TableB') IS NOT NULL BEGIN DROP TABLE #TableB END

Create Table #TableA
(
    Id INT IDENTITY(1, 1)
    , ValueA VARCHAR(10)
)

Create Table #TableB
(
    Id INT IDENTITY(1, 1)
    , TableAID INT
    , ValueB VARCHAR(10)
)

INSERT INTO #TableA VALUES ('Company A'), ('Company B')

INSERT INTO #TableB VALUES (1, '05001'), (1, '05002')
INSERT INTO #TableB VALUES (2, '04001'), (2, '04003')

SELECT
    DISTINCT
    A.ValueA
    , STUFF((SELECT 
                ', ' + B.ValueB
            FROM 
                #TableB B 
            INNER JOIN #TableA A on A.Id = B.TableAID
            WHERE 
                B.ValueB IN ('05001', '05002') --This needs to be part of the main where clause
            FOR XML PATH ('')), 1, 1, '') as TBValue

FROM
    #TableA A
INNER JOIN #TableB B on B.TableAID = A.Id
WHERE
    B.ValueB IN ('05001', '05002') --This will be passed in as the where clause for the View

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL BEGIN DROP TABLE #TableA END
IF OBJECT_ID('tempdb..#TableB') IS NOT NULL BEGIN DROP TABLE #TableB END

The output from the above example give us this:

ValueA     TBValue 
Company A  05001, 05002 

I need to be able to get the sub query to use the same values as the main where clause, OR reconstruct the query to use the primary where clause somehow for the sub data.

Answer 1

You can not pass parameters into a view. They are not designed to accept them. I believe you need to use a Table-Valued (Inline-Table) Function in this case. This will allow you to pass in the values you want and get a 'table' back.

You should be able to use the same code you've already written for your view, with minor changes in the function. You will have to declare your incoming variables, and outgoing table value.

CREATE FUNCTION tvf_SomeFunction 
(   
    -- Add the parameters for the function here
    @Value1 varchar(10), 
    @Value2 varchar(10)
)
RETURNS TABLE 
AS
RETURN 

        DECLARE @TableA TABLE
    (
        Id INT IDENTITY(1, 1)
        , ValueA VARCHAR(10)
    )

    DECLARE @TableB TABLE
    (
        Id INT IDENTITY(1, 1)
        , TableAID INT
        , ValueB VARCHAR(10)
    )

    INSERT INTO @TableA VALUES ('Company A'), ('Company B')

    --INSERT INTO @TableB VALUES (1, '05001'), (1, '05002')
    --INSERT INTO @TableB VALUES (2, '04001'), (2, '04003')

    SELECT
        DISTINCT
        A.ValueA
        , STUFF((SELECT 
                    ', ' + B.ValueB
                FROM 
                    @TableB B 
                INNER JOIN @TableA A on A.Id = B.TableAID
                WHERE 
                    B.ValueB IN (@Value1, @Value2) --This needs to be part of the main where clause
                FOR XML PATH ('')), 1, 1, '') as TBValue

    FROM
        @TableA A
    INNER JOIN @TableB B on B.TableAID = A.Id
    WHERE
        B.ValueB IN (@Value1, @Value2) --This will be passed in as the where clause
    )
    GO

Your final SQL would be as simple as the following:

SELECT *
FROM tvf_SomeFunction('05001', '05002')