How to do a parameterized view with MSSQL
- Microsoft SQL Server up to the current version of MSSQL 2005, does NOT support parameters for views.
- It does make sense from a logical point of view. Views are only virtual tables and tables do not accept any parameter, so why should views allow them?
- It's just not convenient. It would be so much nicer and so much more convenient to allow parameters for the views.
- Fortunately, Microsoft has a work around: inline table-valued function. Functions can have parameters, just like stored procedures.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
Data used
USE sql911;
go
SELECT sales_date, sales_qty, sales_price, emp_short
FROM sales;
go
sales_date sales_qty sales_price emp_short
----------------------- --------------------------------------- --------------------------------------- ----------
2005-12-22 10:01:01.000 10.00 11.75 000310
2006-10-06 10:01:01.000 10.00 11.75 000310
2005-11-30 10:01:01.000 0.00 11.75 000110
2006-10-06 10:01:01.000 10.00 11.75 000310
2005-12-06 11:01:01.000 8.00 11.75 000010
2006-02-28 13:01:01.000 0.00 NULL 000190
2006-03-19 14:01:01.000 5.00 11.75 000110
2006-10-06 10:01:01.000 10.00 11.75 000190
2006-10-06 10:01:01.000 10.00 11.75 000190
2006-04-20 15:01:01.000 4.00 11.75 000030
2006-03-01 16:01:01.000 3.00 11.75 000010
2004-10-06 10:01:01.000 0.00 NULL 000310
2005-11-21 10:01:01.000 9.00 11.75 000010
2006-03-31 17:01:01.000 0.00 11.75 000190
2006-01-22 12:01:01.000 7.00 11.75 000310
2006-02-23 13:01:01.000 6.00 11.75 000110
2005-03-25 14:01:01.000 5.00 11.75 000190
2006-10-31 10:01:01.000 10.00 11.75 000110
2006-10-29 10:01:01.000 3.00 11.75 000110
2007-06-17 17:01:01.000 2.00 11.75 000010
2007-04-18 00:00:00.000 0.00 11.75 000010
2007-05-31 00:00:00.000 5.00 11.75 000030
2007-07-06 00:00:00.000 1.00 11.75 000190
2007-05-03 09:00:00.000 3.00 11.75 000010
2007-05-05 04:00:00.000 1.00 11.75 000010
2006-10-06 10:01:01.000 10.00 11.75 000030
2004-03-06 14:01:01.000 0.00 NULL 000110
2005-04-06 15:01:01.000 4.00 11.75 000010
2005-05-06 16:01:01.000 3.00 11.75 000190
2006-06-06 17:01:01.000 2.00 11.75 000030
2006-07-06 19:01:01.000 1.00 11.75 000310
2006-03-06 14:01:01.000 5.00 11.75 000310
2006-04-06 15:01:01.000 4.00 11.75 000010
2007-05-07 11:00:00.000 2.00 11.75 000010
2006-06-06 17:01:01.000 0.00 NULL 000110
2006-07-06 19:01:01.000 1.00 11.75 000190
2005-10-06 10:01:01.000 10.00 11.75 000030
2007-05-08 15:00:00.000 1.50 11.75 000010
2005-12-06 11:01:01.000 8.00 11.75 000190
2006-01-06 12:01:01.000 7.00 11.75 000030
2007-05-07 11:30:00.000 2.00 10.50 000030
2007-05-08 13:30:00.000 1.00 10.00 000030
2006-07-06 19:01:01.000 1.00 11.75 000010
2007-05-09 14:00:00.000 1.25 10.00 000110
2007-11-06 10:01:01.000 9.00 11.75 000030
2007-05-10 14:00:00.000 5.00 10.00 000190
2007-05-11 14:15:00.000 0.00 10.00 000190
2007-05-12 15:00:00.000 0.00 10.00 000190
2007-05-13 16:00:00.000 1.00 10.00 000190
2007-05-08 05:00:00.000 1.00 10.00 000310
2007-05-07 00:00:00.000 2.00 10.00 000310
2007-05-14 11:00:00.000 3.00 10.00 000310
2006-07-06 00:00:00.000 1.00 11.75 000190
2007-05-11 13:00:00.000 3.50 10.00 000310
2007-05-08 05:00:00.000 0.00 10.00 000010
2007-05-08 07:00:00.000 2.00 10.00 000010
2007-05-08 11:00:00.000 3.00 10.00 000010
(57 row(s) affected)
Inline table-valued function
USE sql911;
go
CREATE FUNCTION dbo.getsales (@emp CHAR(6))
RETURNS TABLE
AS
RETURN (SELECT sales_date, sales_qty, sales_price, emp_short
FROM sales
WHERE emp_short = COALESCE(@emp, emp_short));
go
Command(s) completed successfully.
SELECT * FROM dbo.getsales('000310');
go
sales_date sales_qty sales_price emp_short
----------------------- --------------------------------------- --------------------------------------- ----------
2005-12-22 10:01:01.000 10.00 11.75 000310
2006-10-06 10:01:01.000 10.00 11.75 000310
2006-10-06 10:01:01.000 10.00 11.75 000310
2004-10-06 10:01:01.000 0.00 NULL 000310
2006-01-22 12:01:01.000 7.00 11.75 000310
2006-07-06 19:01:01.000 1.00 11.75 000310
2006-03-06 14:01:01.000 5.00 11.75 000310
2007-05-08 05:00:00.000 1.00 10.00 000310
2007-05-07 00:00:00.000 2.00 10.00 000310
2007-05-14 11:00:00.000 3.00 10.00 000310
2007-05-11 13:00:00.000 3.50 10.00 000310
(11 row(s) affected)
SELECT * FROM dbo.getsales(NULL);
go
sales_date sales_qty sales_price emp_short
----------------------- --------------------------------------- --------------------------------------- ----------
2005-12-22 10:01:01.000 10.00 11.75 000310
2006-10-06 10:01:01.000 10.00 11.75 000310
2005-11-30 10:01:01.000 0.00 11.75 000110
2006-10-06 10:01:01.000 10.00 11.75 000310
2005-12-06 11:01:01.000 8.00 11.75 000010
2006-02-28 13:01:01.000 0.00 NULL 000190
2006-03-19 14:01:01.000 5.00 11.75 000110
2006-10-06 10:01:01.000 10.00 11.75 000190
2006-10-06 10:01:01.000 10.00 11.75 000190
2006-04-20 15:01:01.000 4.00 11.75 000030
2006-03-01 16:01:01.000 3.00 11.75 000010
2004-10-06 10:01:01.000 0.00 NULL 000310
2005-11-21 10:01:01.000 9.00 11.75 000010
2006-03-31 17:01:01.000 0.00 11.75 000190
2006-01-22 12:01:01.000 7.00 11.75 000310
2006-02-23 13:01:01.000 6.00 11.75 000110
2005-03-25 14:01:01.000 5.00 11.75 000190
2006-10-31 10:01:01.000 10.00 11.75 000110
2006-10-29 10:01:01.000 3.00 11.75 000110
2007-06-17 17:01:01.000 2.00 11.75 000010
2007-04-18 00:00:00.000 0.00 11.75 000010
2007-05-31 00:00:00.000 5.00 11.75 000030
2007-07-06 00:00:00.000 1.00 11.75 000190
2007-05-03 09:00:00.000 3.00 11.75 000010
2007-05-05 04:00:00.000 1.00 11.75 000010
2006-10-06 10:01:01.000 10.00 11.75 000030
2004-03-06 14:01:01.000 0.00 NULL 000110
2005-04-06 15:01:01.000 4.00 11.75 000010
2005-05-06 16:01:01.000 3.00 11.75 000190
2006-06-06 17:01:01.000 2.00 11.75 000030
2006-07-06 19:01:01.000 1.00 11.75 000310
2006-03-06 14:01:01.000 5.00 11.75 000310
2006-04-06 15:01:01.000 4.00 11.75 000010
2007-05-07 11:00:00.000 2.00 11.75 000010
2006-06-06 17:01:01.000 0.00 NULL 000110
2006-07-06 19:01:01.000 1.00 11.75 000190
2005-10-06 10:01:01.000 10.00 11.75 000030
2007-05-08 15:00:00.000 1.50 11.75 000010
2005-12-06 11:01:01.000 8.00 11.75 000190
2006-01-06 12:01:01.000 7.00 11.75 000030
2007-05-07 11:30:00.000 2.00 10.50 000030
2007-05-08 13:30:00.000 1.00 10.00 000030
2006-07-06 19:01:01.000 1.00 11.75 000010
2007-05-09 14:00:00.000 1.25 10.00 000110
2007-11-06 10:01:01.000 9.00 11.75 000030
2007-05-10 14:00:00.000 5.00 10.00 000190
2007-05-11 14:15:00.000 0.00 10.00 000190
2007-05-12 15:00:00.000 0.00 10.00 000190
2007-05-13 16:00:00.000 1.00 10.00 000190
2007-05-08 05:00:00.000 1.00 10.00 000310
2007-05-07 00:00:00.000 2.00 10.00 000310
2007-05-14 11:00:00.000 3.00 10.00 000310
2006-07-06 00:00:00.000 1.00 11.75 000190
2007-05-11 13:00:00.000 3.50 10.00 000310
2007-05-08 05:00:00.000 0.00 10.00 000010
2007-05-08 07:00:00.000 2.00 10.00 000010
2007-05-08 11:00:00.000 3.00 10.00 000010
(57 row(s) affected)
