Post reply

Create a view from one store procedure with parameter

  • July 20, 2016 at 2:18 pm

    #400442

    Is it possible to create a view from one store procedure with parameter?

    For example, the following sp is working.

    exec sp_order 'ABC'

    Since sp_order statement is very longer and hard to apply in application.

  • July 20, 2016 at 2:26 pm

    #1891168

    A view is a select statement, nothing else. No procedures, no assignments, no parameters, no variables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 20, 2016 at 2:41 pm

    #1891174

    So no you can't make a view that was something like

    CREATE VIEW MY_VIEW AS(

    EXEC sp_order 'ABC'

    )

    But there are some things you could do depending on what your SP is doing. One option if the stored procedure is just using that parameter to do filtering would be to create the view with no filtering then filter on the view when you select from it in your application so something like

    SELECT * FROM MY_VIEW WHERE FILTER_COLUMN = 'ABC'

    Or use a table valued function and the query from the application would look something like SELECT * FROM MY_FUNCTION('ABC')

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply