How can I create a view with parameters?

Question

question

Matt Whitfield asked Mar 22, '10

How can I create a view with parameters?

I have a sales data view which returns the data from a transaction table as well as the items that are related to those transactions:

SELECT [items].[Type], [items].[Value], [items].[Name], [transactions].[Number]
  FROM [log].[transactions] INNER JOIN 
       [log].[items] on [items].[transactionID] = [transactions].[ID]

However, the need has arisen for the view to return only specific types of items in specific circumstances. What I would like to be able to do is INNER JOIN to the view, but passing in a specific list of item types... Is there any way that I can achieve this functionality without creating a view for each of the item type combinations that are available?

(seeder)

t-sql view seeder-question database-objects

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock commented · Mar 22, 2010 at 01:13 PM

Matt, Seeder < 2000??? cheers

1 ·

Blackhawk-17 commented · Mar 22, 2010 at 02:39 PM

You've sort of asked two questions. The title question and the functionality question.

1 ·

Matt Whitfield ♦♦ commented · Mar 22, 2010 at 03:53 PM

Jonlee - yeah, that was the idea with the seeders... blackhawk - they're only different questions because you know the answer! :)

0 ·

Answer 1 · 2010-03-22T15:12:16Z

Blackhawk-17 answered Mar 22, '10

Tables and views do not take parameters.

And taking a list of parameters threw me for awhile.

Would you consider a SQL 2005+ workaround? Inline Table-valued Functions:

CREATE FUNCTION TransItems (@items varchar(200))
RETURNS table
    AS
    RETURN (SELECT [items].[Type], [items].[Value], [items].[Name], [transactions].[Number]
      FROM [log].[transactions] INNER JOIN 
           [log].[items] on [items].[transactionID] = [transactions].[ID]
      WHERE [log].[items] = @items
    );

EDIT: First SELECT was written, not tested :(

To get back data:

SELECT * FROM TransItems('item1')
UNION
SELECT * FROM TransItems('item2')
UNION
SELECT * FROM TransItems('item3')

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-22T18:27:21Z

Scot Hauder answered Mar 22, '10

Create a user defined table type.
Insert your list of item type IDs into the user defined table type.
Pass it as a table-valued parameter to a user-defined function that returns a table.
In the function select from the view inner-joined with the type IDs in the user defined table.
Select from the function passing the table-valued parameter.

Requires SQL 2008

CREATE TYPE ItemTypes AS TABLE (ItemType int)
go
CREATE FUNCTION dbo.UberSalesDataView(@ItemTypes ItemTypes READONLY)
RETURNS @SalesDataView TABLE ([Type] int,[Value] int,[Name] varchar(50),[Number] varchar(50))
BEGIN
    IF EXISTS (SELECT 1 FROM @ItemTypes) 
    BEGIN
        INSERT @SalesDataView
        SELECT [Type],[Value],[Name],[Number]
        FROM dbo.SalesDataView sdv
        JOIN ItemTypes i ON (i.[ItemType] = sdv.[Type])
    END
    ELSE
    BEGIN
        INSERT @SalesDataView
        SELECT [Type],[Value],[Name],[Number]
        FROM dbo.SalesDataView
    END
RETURN
END;
go
DECLARE @Params AS ItemTypes
INSERT @Params
SELECT 42
SELECT * FROM dbo.UberSalesDataView(@Params)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question