Using CTE in Scalar Function

  • Jazzcatone2

    Valued Member

    Points: 63

    I am trying to use a CTE (Common Table Expression) within a ScalarFunction . Am I able to do this. I am get several red squiggly line errors right now.  (“Incorrect syntax near Declare” ,”Invalid column name DateEffective”,”Invalid Object Name inceptionCTE”,

    “Invalid column name PriorPolicy”) Is there a way to do this and make SQL SERVER happy ? Can anybody tell me what I would need to do with this function ? Any direction or advice would be greatly appreciated.

    –Jason

    USE [Premdat]
    GO
    /****** Object: UserDefinedFunction [dbo].[fnJasonCreate] Script Date: 4/15/2019 3:24:10 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[fnJasonCreate]
    ( -- Add the parameters for the function here
    @pol VARCHAR(10)
    )
    RETURNS Datetime
    AS

    BEGIN

    ;WITH InceptionCTE AS (
    SELECT p.[Policy], p.PolicyID, p.DateEffective, p.DateExpiration, p.QuoteNum, p.PriorPolicy
    FROM PolicyData p WITH(NOLOCK)
    WHERE p.[PolicyID] = @pol
    UNION ALL
    SELECT p.[Policy], p.PolicyID, p.DateEffective, p.DateExpiration, p.QuoteNum, p.PriorPolicy
    FROM PolicyData p WITH(NOLOCK)
    INNER JOIN InceptionCTE cd ON cd.PriorPolicy = p.[Policy]
    )

    DECLARE @InceptionDate DATETIME
    SET @InceptionDate = (SELECT DateEffective FROM InceptionCTE WHERE PriorPolicy = 'New')

    RETURN @InceptionDate
    END

     

     

  • Lynn Pettis

    SSC Guru

    Points: 442080


    Couldn’t test it, but give this a try:

    USE [Premdat]
    GO
    /****** Object: UserDefinedFunction [dbo].[fnJasonCreate] Script Date: 4/15/2019 3:24:10 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[fnJasonCreate]
    ( -- Add the parameters for the function here
    @pol VARCHAR(10)
    )
    RETURNS Datetime
    AS

    BEGIN
    DECLARE @InceptionDate DATETIME;

    WITH InceptionCTE AS (
    SELECT
    p.[Policy]
    , p.PolicyID
    , p.DateEffective
    , p.DateExpiration
    , p.QuoteNum
    , p.PriorPolicy
    FROM
    PolicyData p
    WHERE
    p.[PolicyID] = @pol
    UNION ALL
    SELECT
    p.[Policy]
    , p.PolicyID
    , p.DateEffective
    , p.DateExpiration
    , p.QuoteNum
    , p.PriorPolicy
    FROM
    PolicyData p
    INNER JOIN InceptionCTE cd
    ON cd.PriorPolicy = p.[Policy]
    )
    SELECT @InceptionDate = DateEffective FROM InceptionCTE WHERE PriorPolicy = 'New';

    RETURN @InceptionDate
    END
    • This reply was modified 1 week, 1 day ago by  Lynn Pettis.
    • This reply was modified 1 week, 1 day ago by  Lynn Pettis.
  • ZZartin

    SSC-Dedicated

    Points: 30225

    Keep in mind if that CTE returns more than 1 record you might get some unexpected results.

  • Lynn Pettis

    SSC Guru

    Points: 442080

    ZZartin wrote:

    Keep in mind if that CTE returns more than 1 record you might get some unexpected results.

    True, but the OP also needs to know how to correctly use a CTE as part of a query.

  • Jazzcatone2

    Valued Member

    Points: 63

    This helped . Thank you very much

  • Jonathan Szeto

    SSC Journeyman

    Points: 97

    To clarify why you got an error:

    After the CTE definition, you had a DECLARE variable statement. You can only use a CTE within the context of DML language (SELECT, INSERT, UPDATE, DELETE, MERGE).

Viewing 6 posts - 1 through 6 (of 6 total)

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