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

Answer 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
)

Answer 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