Need an alternative way for using parameters in views

Question

Need an alternative way for using parameters in views

mikemartineac

SSC Veteran

Points: 275
More actions
March 4, 2012 at 4:39 pm

#251886

Hello,
In a stored procedure, I have a view as my data source. Unfortunately, views restrict the use of parameters. In my case, I need to get a value into the view from which to filter the data. This needs to happen inside of the view as this process needs to be isolated. What are my options? What about a nested stored procedure? Thanks in advance for any assistance.
Mike

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

My guess would be that since you're absolutely sure that you need to use a parameter, then don't use the view because views don't take parameters. Rewrite the view as an inline Table Value Function which CAN accept parameters and use it in place of the view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

In my case, I need to get a value into the view from which to filter the data.

Are you using this parameter as a where clause? It sounds like that is what you are trying to do here. Just create your view with no where clause. Then when you select from it use the where clause.

create view MyView as

(

select [Column List] from MyTable

)

--then to use it

select [Column List] from MyView where MyColumn = @TheParameterHere

--obviously you don't want to use select *

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eric M Russell SSC Guru Points: 125562 More actions · Answer 3

mikemartineac (3/4/2012)
Hello,
In a stored procedure, I have a view as my data source. Unfortunately, views restrict the use of parameters. In my case, I need to get a value into the view from which to filter the data. This needs to happen inside of the view as this process needs to be isolated. What are my options? What about a nested stored procedure? Thanks in advance for any assistance.
Mike

What is meant by saying the "process needs to be isolated"?

If there is some row selection that should always be applied to the view, for example excluding all rows that have been logically deleted, then you can place that condition in the WHERE clause of the view's SELECT statement.

You can look into how table valued functions work and consider it as an option, but from my experience I rarely see cases where implementing a TVF offers a functional or performance advantage over a view.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

EdVassie SSC Guru Points: 60476 More actions · Answer 4

select [Column List] from MyView where MyColumn = @TheParameterHere

In SQL Server, this syntax is often just as efficient as including the WHERE clause within the view definition itself. If you run this in Oracle or Access it will always be less efficient than including the WHERE clause in the view.

This is because the SQL Server optimiser performs a process of query statement consolidation before generating the access plan, so that a single SELECT statement can be run against the tables specified in the view. This also means that nested views can be just as efficient in SQL Server as a single view or a query against the underlying tables.

In Oracle or Access, the view is always materialised before any external WHERE clauses are applied, so performance is always worse. If you use nested views in this environment, then you get multiple levels of view materialisation, which further harms performance.

In order to know what is good or bad for a given RDBMS, you need to know how it works internally.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara