Can we create Parameterized View in SQL Server?

SQL Server doesn’t have parameterized view but close to it they came up with Table Value User Defined Function in SQL Server 2000.

If it did exist, the syntax would be 

create view ParamterView

(@empName varchar(10))

AS

 select * from employee where emp_name like @empName

Msg 102, Level 15, State 1, Procedure ParamterView, Line 2

Incorrect syntax near '@empName'.

Msg 137, Level 15, State 2, Procedure ParamterView, Line 4

Must declare the scalar variable "@empName".

But unfortunately we don’t have luxury to create parameterized view like this.

But we have alternative approach to stimulate the same using Inline table value function

create function pv_studentInfo (@student_id int)

returns table

as

return

(

    select * from Student

    where id = @student_id

)

Go

Lets call this view using select statement.

select * from pv_studentInfo(1)

id          name

----------- ------------

1           vikram

(1 row(s) affected)

View offer performance benefit against Function by allowing indexes on it, where as you cannot create indexes on Function but yes you can wrap indexed indexed view in Function.

Your code silently induce oxygen to your business partner.

 

Post Reference: Vikram Aristocratic Elfin Share