Creating VIEW with dynamic parameter

Question

User1992540083 posted

I want to create a view with dynamic params e.g. lets say

CREATE VIEW ABC 

AS SELECT *

FROM TEMP WHERE DATE > @Variable

The only way I could think of was to create a custom defined function that would return this view as table

e.g. 

CREATE FUNCTION CUSTOMFUNCTION(@Variable nvarchar(max))
RETURNS table
AS
RETURN (WITH customView (COLUMNA, COLUMNB) 
AS
(

AS SELECT *

FROM TEMP WHERE DATE > @Variable

)

and then call it in SQL query

SELECT * from CUSTOMFUNCTION (20151212);

Is there any other way to do it ? Such that we can send parameter to VIEW in SQL ? May be by using EXISTS @param ?

Thanks,

Aiden

Answer 1

User726682967 posted

Hi Bhattia,

we can't create parameterized view, instead of creating view go with stored procedure.

Answer 2

User753101303 posted

Hi,

Not sure about the problem you have with parameters. It should be as usual when calling SQL Statement ie

SELECT * FROM CustomView(@prm) and then you feed the SqlCommand with a prm parameter. Thes syntax could perhaps be slightly shorten :

CREATE FUNCTION YouView(@prm) RETURNS TABLE AS RETURN
(
SELECT A,B,C FROM Whatever WHERE value=@prm
)

Technically speaking this a function but to me a view with a parameter IS basically a result set returning function (it doesn't have to be a table, you can reuse a "classic" view in your function).

As for SP it depends if you plan to reuse that in other views/statements or if not (it's easier to reuse a function result set, SPs are likely best when the result is always returned to the client side).