locked
Creating VIEW with dynamic parameter RRS feed

  • 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

    Friday, November 27, 2015 8:36 AM

Answers

  • User726682967 posted

    Hi Bhattia,

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 27, 2015 11:12 AM
  • 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).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 27, 2015 12:22 PM

All replies

  • User726682967 posted

    Hi Bhattia,

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 27, 2015 11:12 AM
  • 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).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 27, 2015 12:22 PM