Creating a SQL View with parameters

Question

I have a SQL Query that accepts parameters. Now When I'm trying to include that query into a view, I'm facing an error because a view cannot hold a parameters just like an SP or a function can.

Hence if I had to create the view that had to contain the parameters, Is there someway that it is possible?

Many Thanks

Solution 1

I don't think so you can create a parameter in a View .But you can create a function which takes input parameter like the one below .

CREATE FUNCTION dbo.Sample (@Parameter varchar(10))
RETURNS TABLE
AS
RETURN
(
 SELECT Field1, Field2,....
 FROM YourTable
 WHERE Field3 = @Parameter
)

Solution 2

No, from MSDN

Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:

To focus, simplify, and customize the perception each user has of the database.

As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.

To provide a backward compatible interface to emulate a table whose schema has changed.

So, basically, this acts just like a table, and the only way that you can add "parameters" to a table is via filter statements when accessing the view

Comments

[Suncat2000]

That is the most rational explanation I've heard for prohibiting parameterized views. Still stinks, though, when you need parameters to filter subqueries.

[Suncat2000]

While you were being condescending, you missed the fact that you created a stored procedure, not a view.

[user2864740]

An ITVF (as opposed to a TVF/SF) is effectively a view in terms of how it is merged into the final query.

[vaitrafra]

well thanks. I searched for parametrized view and the smarta** says "you stooped, use this" and writes a stored procedures.