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?


more ▼

asked Mar 22, 2010 at 01:11 PM in Default

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

Matt, Seeder < 2000??? cheers

Mar 22, 2010 at 01:13 PM sp_lock

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

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

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

Mar 22, 2010 at 03:53 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

  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


SELECT * FROM dbo.UberSalesDataView(@Params)

more ▼

answered Mar 22, 2010 at 06:27 PM

avatar image

Scot Hauder
6.5k 13 16 22

(comments are locked)
10|1200 characters needed characters left

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))
    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')
SELECT * FROM TransItems('item2')
SELECT * FROM TransItems('item3')
more ▼

answered Mar 22, 2010 at 03:12 PM

avatar image

12.1k 30 36 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 22, 2010 at 01:11 PM

Seen: 37563 times

Last Updated: Mar 22, 2010 at 01:36 PM

Copyright 2018 Redgate Software. Privacy Policy