Using CTE in Scalar Function

Question

Using CTE in Scalar Function

SSC Enthusiast

Points: 173

April 15, 2019 at 8:34 pm

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

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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 5 years ago by Lynn Pettis.
This reply was modified 5 years ago by Lynn Pettis.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 2

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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 SSC Enthusiast Points: 173 More actions · Answer 4

Jazzcatone2

SSC Enthusiast

Points: 173

April 15, 2019 at 9:33 pm

#3389298

This helped . Thank you very much

Jonathan Szeto SSC Enthusiast Points: 120 More actions · Answer 5

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).

mark.hausmann Newbie Points: 8 More actions · Answer 6

Semicolon is missing before the WITH, ohterwise ok

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 7

mark.hausmann wrote:

Semicolon is missing before the WITH, ohterwise ok

semi-colon is a statement terminator. Why would you need a semi-colon 'before' the WITH?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

Instead of worrying about where the bloody semi-colon goes, someone needs to convince this fellow that this absolutely needs to be converted to a single value iTVF or "iSF" and how to call it because I know how this "policy number" stuff works in real life and it's going to beat the crap out of the OP's server.

@jason... as Lynn said, he wrote some code but hasn't tested it because he has no test data. Please see the article at the first link in my signature line below for one way to provide the "Readily Consumable" test data that would help us help you a whole lot more.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)