passing parameter to view from sp

Question

I am using below query in my stored procedure.

F_Chem_name and Language are input parameters.

Is it possible to create a view for this below query and pass these input parameters to the view from stored procedure?.

How can i pass parameter to view from stored procedure?

WITH CTE AS
(
SELECT F_PRODUCT
      ,n.F_CAS_NUMBER
      ,c.F_COMPONENT_ID
FROM dbo.PDF_MSDS

    CROSS APPLY (SELECT value as F_CAS_NUMBER
                 FROM STRING_SPLIT(F_CAS_NUMBERS, '¿')
                 WHERE value <> '') as n

    CROSS APPLY (SELECT value as F_COMPONENT_ID
                 FROM STRING_SPLIT(F_COMPONENT_IDS, '¿')
                 WHERE value <> '') as c
)
SELECT CTE.F_PRODUCT
      ,COM.F_Cas_Number
      ,COM.F_Component_Id
      ,COM.F_Chem_Name
FROM dbo.Components COM

    INNER JOIN CTE
        ON CTE.F_CAS_NUMBER = COM.F_Cas_Number
            AND CTE.F_COMPONENT_ID = COM.F_Component_Id

WHERE COM.F_Chem_Name LIKE @Chem_Name
    AND Language = @Language

What are you actually trying to do here? You can't pass parameters to a view, though you can apply a where filter once the view is defined and you are selecting data from it. — iamdave
– iamdave, Commented Aug 17, 2018 at 10:20

Dan Guzman · Accepted Answer · 2018-08-17 10:30:34Z

1

Your options are:

1) create the view without the WHERE clause and specify the WHERE clause in the proc when selecting from the view

2) convert the view to a table-valued function, passing the values as parameters.

answered Aug 17, 2018 at 10:30

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

passing parameter to view from sp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related