SQL 函数返回 View 或表

Question

我有两张 table 。一个是引用表，其中包含一组字符串名称 TubName:

| Id |  SiteID | TubName | TubVol |
|----|---------|---------|--------|
|  1 | Site001 |   Tub 1 |   1000 |
|  2 | Site001 |   Tub 2 |   1000 |
|  3 | Site001 |   Tub 3 |   1000 |
|  4 | Site001 |   Tub 4 |   1000 |
|  5 | Site001 |   Tub 5 |   1000 |
|  6 | Site001 |   Tub 6 |   1000 |

每天都会运行一份报告，显示存在哪些 TubName:

| Id |  SiteID |     Date |   Tub |
|----|---------|----------|-------|
|  1 | Site001 | 20/06/15 | Tub 1 |
|  2 | Site001 | 20/06/15 | Tub 1 |
|  3 | Site001 | 20/06/15 | Tub 3 |
|  4 | Site001 | 20/06/15 | Tub 4 |
|  5 | Site001 | 20/06/15 | Tub 5 |

如果比较两个表，则 Tub 2 和 Tub 6 不存在。

我创建了返回“未测试”浴缸的简单查询:

SELECT r.TubName
FROM Tubs r 
WHERE TubName NOT IN(SELECT b.Tub FROM Tests b where SiteID = 'Site001' AND Date = '20/06/15')

我真正想要的是一个函数，我可以通过它以编程方式输入 SiteID 和 Date 作为参数。我试过这样的事情:

CREATE FUNCTION [dbo].[TubsNotTested](@id nvarchar(128), @testdate nvarchar(MAX))
RETURNS view
AS
BEGIN
    RETURN (
        SELECT r.TubName 
        FROM [dbo].[Tubs] r 
        WHERE TubName NOT IN(
            SELECT b.Tub 
            FROM dbo.Tests b 
            where(SiteID = id AND Date = testdate)
        )
    )
END

但是当 table 是返回的对象时我得到错误:

A RETURN statement with a return value cannot be used in this context.

或者当我尝试返回一个 View 时:

Incorrect syntax near the keyword 'view'.

Answer 1

你可以尝试这样的事情:

CREATE FUNCTION [dbo].[TubsNotTested](@id nvarchar(128), @testdate nvarchar(MAX))
RETURNS TABLE
AS RETURN(
    SELECT r.TubName 
    FROM [dbo].[Tubs] r 
    LEFT JOIN (
        SELECT b.Tub 
        FROM dbo.Tests b 
        WHERE (SiteID = @id AND Date = @testdate)
    ) as tubs
        ON r.TubName = b.Tub
    WHERE tubs.Tub IS NULL
)

如果您需要在您的函数中添加更多逻辑，您可以构建一个多语句函数:

CREATE FUNCTION [dbo].[TubsNotTested](@id nvarchar(128), @testdate nvarchar(MAX))
RETURNS @result TABLE(
    TubName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @result(TubName)
    SELECT r.TubName 
    FROM [dbo].[Tubs] r 
    LEFT JOIN (
        SELECT b.Tub 
        FROM dbo.Tests b 
        WHERE (SiteID = @id AND Date = @testdate)
    ) as tubs
        ON r.TubName = b.Tub
    WHERE tubs.Tub IS NULL

    RETURN
END

但是顺便说一句。当心。 SQL Server 函数，可能成为更大请求的瓶颈。请记住这一点。