Problem Statement

You have a need to extract data from a table or view with 1 or more parameters.  The challenge is that the parameter changes based on various factors.

The solution often is to write this, not as a view, but as a Stored Procedure or a Table Valued Function (TVF) that accepts a parameter and inserts the value of the parameter wherever required in your code. This typically should be for a scalar value, such as a Foreign Key or Primary Key lookup value.

The problem is further that there are many times when you would NOT want to use a stored procedure or a TVF because Stored Procedures, for example, require the GRANTING of permissions to execute and many applications don't work well with either Stored Procedures or TVF as part of the classic ODBC connection.  Finally, the user may know exactly how to connect to your data using a product such as Excel, and know that they simply need to connect to a given view, but assumes a skill-level at the user to make effective use of a TVF or Stored Procedure.

The Down Side of using a VIEW is the fact that parameters cannot be passed into your query.  You cannot declare variables within your view and you can't use the variable values anywhere within your view.

What Exactly is a VIEW

So many of our developers use views on a regularly basis without realizing that it is a simply a restrictive type of Function or Stored Procedure that can accept an optional parameter.  I've heard so many SQL Developers saying that they never use Views, they have a policy of only using Stored Procedures, which is strange, because the stored procedure would start of with a simple query, such as:

select * from mytable

This simple query can be created as a View, Stored Procedure or Function with no performance gains or losses.  The primary problem being that it may be necessary to add some programming components to the query such as a DO WHILE statement, a DECLARE statement or a multi-stage query that first inserts results into an in-memory table before eventually delivering a dataset to the user.  In these cases the Stored Procedure is a better option, otherwise the VIEW is a better option.

Creating a Sample Table

The conversation here is based off a table that has been created in your database such as the following sample script:

CREATE TABLE [dbo].[MyTables](
[MyTableID] [INT] IDENTITY(1,1) NOT NULL,
[Period] [INT] NULL,
[OtherInfo] [VARCHAR](50) NULL,
[MoreInfo] [VARCHAR](50) NULL,
CONSTRAINT [PK_MyTables] PRIMARY KEY CLUSTERED 
(
[MyTableID] ASC
)WITH (
PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

The data in this table, looks like this:

MyTableID |Period|OtherInfo            |MoreInfo
1         |2018  |Blah, blah bla Lorem |Ipsum Somethingum Extramus Etc
2         |2019  |Blah, blah bla Lorem |Ipsum Somethingum Extramus Etc
3         |2020  |Blah, blah bla Lorem |Ipsum Somethingum Extramus Etc
4         |2021  |Blah, blah bla Lorem |Ipsum Somethingum Extramus Etc
5         |2022  |Blah, blah bla Lorem |Ipsum Somethingum Extramus Etc
6         |2023  |Blah, blah bla Lorem |Ipsum Somethingum Extramus Etc

Now, what if you had a simple view like

Select * from MyTables where Period=2022

This would simply return the 5th row.

5  |2022 |Blah, blah bla Lorem |Ipsum Somethingum Extramus Etc

To convert this simple query to a stored procedure, TVF or view you could execute one of the following:

--Stored Procedure ----------------
Create Procedure proc_TableByPeriod
as
Select * from MyTables where Period=2202
--Table Valued Function ----------------
Create Function fn_TableByPeriod()
RETURNS table
as
RETURN (SELECT * from MyTables where Period=2022)
--View ----------------
Create View v_TableByPeriod
as
Select * from MyTables where Period=2202

To extract data from the Stored Procedure you would issue the following:

EXEC dbo.proc_TableByPeriod

To extract data from the Table Valued Function you would issue the following command

SELECT * FROM fn_TableByPeriod()

Finally, to extract data from the view you would simply issue the following:

SELECT * FROM v_TableByPeriod

The execution plans for these 3 methods are identical.  There is ZERO performance gain between these three options.  The challenge lies in when the user wants to look find information for an alternate period - possibly through the use of a variable parameter. For this to happen with either the TVF or the Stored Proc you have to edit the  script to reflect the new period, which is an inglorious pain the the nether regions - right?

With the Table Valued Functions you can then pass a parameter of the period into the select statement as follows:

Alter FUNCTION fn_TableByPeriod(
@Period int
)
AS
RETURN
(SELECT * FROM MyTables WHERE Period=@Period)

This will then be used as follows:

select * from fn_MyTablesByPeriod(2202)

Obviously you could similarly edit the Stored Procedure in the same way and execute the Stored Procedure as follows:

EXEC dbo.proc_TableByPeriod 2022

The problem is that your users and systems now all need to learn to use a different thought process and if the environment doesn't like using Stored Procedures or TVF,s then it becomes a bit of a pain again.

I solve this by creating a Filtered Parameters table that stores the variable values.  I then track the most common filtered by stuff and keep it in a table.  The table, in this scenario, would have a column called Period and would consist of a single row of data.  My view is then amended as follows:

Alter FUNCTION fn_TableByPeriod(
@Period int
)
AS
RETURN
(SELECT * FROM MyTables WHERE Period=(SELECT TOP 1 Period FROM dw.FilterPeriod))

Now my View is driven by dynamically changing parameters and I then provide an interface to change the parameter to the user.

Changing the [Period], the [OtherInfo] or any other column, can be done in the same way using the Filtered Parameters table and can be used liberally throughout all coding applications.

Where the parameter is dependent on the given user I would add rows of parameters used by each user and filter by ORIGINAL_LOGIN() or some other mechanism that represents the CurrentUser() (subject for another conversation)

I find that this has identical performance to Stored Procedures or TVFs, but is far more robust and is much more friendly to applications that rely on simple SELECT statements.

There are many ways to update the parameters table, which is beyond the scope of this article, given that I hoped to demonstrate how I get around the issue of passing parameters into a query in a sustainable manner.