CODEWITHSUNDEEP
sqlsql-serversql-server-2014
biggboss2019
biggboss2019

Reputation: 300

How to call a function within a new function in SQL Server 2014

I have created a function called PV which shows me v_id with lowest cost. Now I would like to call that function and would like SQL to display 

p_name, p_description, QH associated

with this v_id. My question is how do I do that?

My tables are below along with my function code:

Code:

CREATE FUNCTION PV (@ptid AS INT)
RETURNS INT
AS
BEGIN
    DECLARE @vid AS INT
    DECLARE @min AS INT

    SELECT 
        ptid = VP.P_ID, @vid = VP.V_ID,
        @min = MIN(VP.COST)
    FROM 
        VPr
    GROUP BY 
        VP.P_ID, VP.V_ID
    HAVING 
        MIN(VP.COST) < 25

    RETURN @vid
END;
GO

Note: if I execute this function, along with my input for ptid as 9, it will return the correct v_id. To reiterate: I would like to call that function along with p_name, p_description, and qh

I am new to SQL functions. Kindly provide me an explanation.

Thanks in advance!

Upvotes: 0

Views: 59

Answers (1)

Chris Mack
Chris Mack

Reputation: 5208

Does this do what you're looking for:

SELECT
    p_name
    , p_description
    , qh
    , v_id
FROM
    (
        SELECT
            P.p_name
            , P.p_description
            , P.qh
            , vp.v_id
            , ROW_NUMBER() OVER (ORDER BY vp.cost) R
        FROM
            P
            JOIN vp ON P.p_id = vp.p_id
        WHERE vp.v_id = dbo.PV(9) -- you may need to alter the schema from 'dbo'
    ) Q
WHERE R = 1

Upvotes: 1

Related Questions