CODEWITHSUNDEEP
sqlsql-server
tomdertech
tomdertech

Reputation: 507

SQL Function returning a View or Table

I have two tables. One is a reference table which contains a group of string names 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 |

Each day a report is run which shows which TubNames are present:

| 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 |

If the two tables are compared then Tub 2 and Tub 6 is not present.

I have created the simple query which returns the "untested" tubs:

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

What I really want is a function whereby I can programmatically input the SiteID and Date as arguments. I have tried something like this:

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

But I get the error when a table is the returned object:

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

Or this when I try and return a View:

Incorrect syntax near the keyword 'view'.

Upvotes: 2

Views: 12268

Answers (2)

Ionic
Ionic

Reputation: 3935

You can try something like that:

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
)

If you need to put further logic into your function, you can build a multistatement function:

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

But by the way. Be careful. SQL Server function, can be a bottleneck on bigger requests. Keep this in mind.

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1270361

Besides the RETURNS view, you also need to specify the parameters correctly:

CREATE FUNCTION [dbo].[TubsNotTested](@id nvarchar(128), @testdate nvarchar(MAX))
RETURNS TABLE
AS
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)
           )
      );

The BEGIN/END are unnecessary for an inline table-valued function.

Upvotes: 1

Related Questions