Last post Nov 27, 2015 12:22 PM by PatriceSc
Nov 27, 2015 08:36 AM|m.bhattia|LINK
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
CREATE FUNCTION CUSTOMFUNCTION(@Variable nvarchar(max))
RETURN (WITH customView (COLUMNA, COLUMNB)
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 ?
Nov 27, 2015 11:12 AM|maradana26|LINK
we can't create parameterized view, instead of creating view go with stored procedure.
Nov 27, 2015 12:22 PM|PatriceSc|LINK
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).