question

Matt Whitfield avatar image
Matt Whitfield asked

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-sqlviewseeder-questiondatabase-objects
3 comments
10 |1200

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

Matt, Seeder < 2000??? cheers
1 Like 1 ·
You've sort of asked two questions. The title question and the functionality question.
1 Like 1 ·
Jonlee - yeah, that was the idea with the seeders... blackhawk - they're only different questions because you know the answer! :)
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered

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

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

Scot Hauder avatar image
Scot Hauder answered
  1. Create a user defined table type.
  2. Insert your list of item type IDs into the user defined table type.
  3. Pass it as a table-valued parameter to a user-defined function that returns a table.
  4. In the function select from the view inner-joined with the type IDs in the user defined table.
  5. 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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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