When working with SQL Server, there are often situations where you need to extract data from a table or view with one or more parameters. However, the challenge arises when the parameter value changes based on various factors. In such cases, using a stored procedure or a table-valued function (TVF) that accepts a parameter can be a solution. But what if you don’t want to use a stored procedure or a TVF?

One option is to use a view. Many developers use views regularly without realizing that they are simply a restrictive type of function or stored procedure that can accept an optional parameter. Views can be a better option when you only need to add some programming components to the query, such as a DO WHILE statement or a DECLARE statement. However, the downside of using a view is that you cannot pass parameters into your query or use variable values within the view.

To illustrate this, let’s consider a sample table called “MyTables” with columns like “Period,” “OtherInfo,” and “MoreInfo.” If you had a simple view like “SELECT * FROM MyTables WHERE Period = 2022,” it would return the 5th row of data.

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 = 2022

-- 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 = 2022

To extract data from the stored procedure, you would use the following command:

EXEC dbo.proc_TableByPeriod

To extract data from the table-valued function, you would use the following command:

SELECT * FROM fn_TableByPeriod()

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

SELECT * FROM v_TableByPeriod

The execution plans for these three methods are identical, with no performance gain or loss. However, the challenge arises when the user wants to find information for an alternate period using a variable parameter. With the table-valued function, you can pass a parameter into the select statement, like this:

ALTER FUNCTION fn_TableByPeriod(@Period INT)
AS
RETURN
(SELECT * FROM MyTables WHERE Period = @Period)

This allows you to execute the function with a specific period, such as:

SELECT * FROM fn_TableByPeriod(2022)

Similarly, you could edit the stored procedure in the same way and execute it with a parameter:

EXEC dbo.proc_TableByPeriod 2022

However, this approach requires users and systems to learn a different thought process, and it may not be suitable for environments that don’t support stored procedures or TVFs.

To overcome this challenge, you can create a filtered parameters table that stores the variable values. By tracking the most common filtered values and keeping them in a table, you can dynamically change the parameters in your view. For example, you can modify the view as follows:

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

Now, your view is driven by dynamically changing parameters, and you can provide an interface for users to change the parameter value. You can apply the same approach to other columns, such as “OtherInfo,” by using the filtered parameters table throughout your coding applications.

This method has identical performance to stored procedures or TVFs but is more robust and user-friendly for applications that rely on simple SELECT statements. Updating the parameters table can be done in various ways, which is beyond the scope of this article. The main goal here was to demonstrate how to pass parameters into a query in a sustainable manner.

By understanding the different options available and leveraging the power of views, stored procedures, and table-valued functions, you can effectively extract data from SQL Server tables and views with varying parameters.