Unresolved error code

Question

I'm attaching the code for a CTE that works fine on it's own. I'm attempting to run it from within a function which, I have never done and get errors that I simple can fix. More, eyes on this problem would be appreciated. I don't feel the need to explain the CTE code since it works fine the issue is it being rapped into a function. Here is the code and SQL Server 2008 R2 error messages:

USE Med
Go

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Robert G. Seminario
-- Create date: 5/19/2017
-- Description: Joint Credit Not Checked
-- =============================================
CREATE FUNCTION [dbo].[fn_JointCreNotCk] 

    (@din as Nvarchar (40), @jin as Nvarchar(30))   -- Pass in Donor_ID_Number and Joint_ID_Number

RETURNS Nvarchar(40)

AS
BEGIN
Return
(

Go

    With dn_cte (TRANSACTION_ID, Donor_ID_Number, ID_Number, DonorFound)

    AS
(
        Select
        olg.TRANSACTION_ID, olg.Donor_ID_Number, lmc.Id_Number, 
        (CASE When lmc.Id_Number = @din THEN '1' ELSE '0' END) as DonorFound
        from OnlineGivingDailyLoad as olg
        left outer join LMC_ENTITY as lmc
        on lmc.Id_Number = olg.Donor_ID_Number

),
 Jt_cte as

(
        Select 
        olg.TRANSACTION_ID, olg.Joint_ID_Number, lmc.Id_Number,
        CASE When lmc.Id_Number = @jin THEN '1' ELSE '0' END As JointFound
        from OnlineGivingDailyLoad as olg
        left outer join LMC_ENTITY as lmc
        on lmc.Id_Number = olg.Joint_ID_Number

),
  dnjnt_cte as

(

        Select 
        olg.TRANSACTION_ID, olg.Donor_ID_Number, 
        (CASE WHEN e.jnt_Gifts_ind = 'Y' THEN '1' ELSE '0' END)as D_JntGI
        from OnlineGivingDailyLoad as olg
        left outer join Advance.dbo.ENTITY as e
        on e.ID_NUMBER = olg.Donor_ID_Number


),
  Jtjnt_cte as

(

        Select 
        olg.TRANSACTION_ID, olg.Joint_ID_Number, 
        (CASE WHEN e.jnt_Gifts_ind = 'Y' THEN '1' ELSE '0' END)as J_JntGI
        from OnlineGivingDailyLoad as olg
        left outer join Advance.dbo.ENTITY as e
        on e.ID_NUMBER = olg.Joint_ID_Number
),
  JtCkNotCk as

(
        Select dn_cte.TRANSACTION_ID, 
        (Case WHEN cast(dn_cte.DonorFound as int) + cast(Jt_cte.JointFound as int) = 2 THEN 1 ELSE 0 END)as BothFound,
        (CASE WHEN cast(dnjnt_cte.D_JntGI as int) + cast(Jtjnt_cte.J_JntGI as int) = 2 THEN 1 ELSE 0 END)as JointG
        from dn_cte 
        inner join jt_cte
        on Jt_cte.TRANSACTION_ID = dn_cte.TRANSACTION_ID
        inner join dnjnt_cte 
        on dnjnt_cte.TRANSACTION_ID = dn_cte.TRANSACTION_ID
        inner join Jtjnt_cte
        on Jtjnt_cte.TRANSACTION_ID = dn_cte.TRANSACTION_ID
)

Select TRANSACTION_ID, (CASE WHEN (BothFound + JointG) = 2 THEN 1 ELSE 0 END) AS Final
from JtCkNotCk 
order by 1

)

END

Go

Now for the error messages:

Msg 102, Level 15, State 1, Procedure fn_JointCreNotCk, Line 16
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@din".
Msg 137, Level 15, State 2, Line 19
Must declare the scalar variable "@jin".
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 48
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 68
Incorrect syntax near ')'.

Help is appreciated.

Answer 1

First: You have go right after begin return(.

Second: You are writing the function as if it was an inline table valued function but you have declared a scalar return.

Without trying to untangle what you have here (though it looks like there is probably a better way to do what you are trying to do):

This would be closer to an inline table valued function:

use Med go
set ansi_nulls, quoted_identifier on;
go
 -- ============================================= 
 -- Author: Robert G. Seminario 
 -- Create date: 5/19/2017 
 -- Description: Joint Credit Not Checked 
 -- ============================================= 
 -- Pass in Donor_ID_Number and Joint_ID_Number
CREATE FUNCTION [dbo].[fn_JointCreNotCk] (@din as nvarchar(40), @jin as nvarchar(30)) 
returns table as return (
   with dn_cte(TRANSACTION_ID, Donor_ID_Number, ID_Number, DonorFound) as (
     select 
        olg.TRANSACTION_ID
      , olg.Donor_ID_Number
      , lmc.Id_Number
      , (case when lmc.Id_Number = @din then 1 else 0 end) as DonorFound
     from OnlineGivingDailyLoad as olg
       left join LMC_ENTITY as lmc on lmc.Id_Number = olg.Donor_ID_Number
     )
    , Jt_cte as (
     select olg.TRANSACTION_ID
      , olg.Joint_ID_Number
      , lmc.Id_Number
      , case when lmc.Id_Number = @jin then 1 else 0 end as JointFound
     from OnlineGivingDailyLoad as olg
      left join LMC_ENTITY as lmc on lmc.Id_Number = olg.Joint_ID_Number
     )
    , dnjnt_cte as (
     select olg.TRANSACTION_ID
      , olg.Donor_ID_Number
      , (case when e.jnt_Gifts_ind = 'Y' then 1 else 0 end) as D_JntGI
     from OnlineGivingDailyLoad as olg
      left join Advance.dbo.ENTITY as e on e.ID_NUMBER = olg.Donor_ID_Number
     )
    , Jtjnt_cte as (
     select olg.TRANSACTION_ID
      , olg.Joint_ID_Number
      , (case when e.jnt_Gifts_ind = 'Y' then 1 else 0 end) as J_JntGI
     from OnlineGivingDailyLoad as olg
      left join Advance.dbo.ENTITY as e on e.ID_NUMBER = olg.Joint_ID_Number
     )
    , JtCkNotCk as (
     select dn_cte.TRANSACTION_ID
      , (case when cast(dn_cte.DonorFound as int) + cast(Jt_cte.JointFound as int) = 2 then 1 else 0 end) as BothFound
      , (case when cast(dnjnt_cte.D_JntGI as int) + cast(Jtjnt_cte.J_JntGI as int) = 2 then 1 else 0 end) as JointG
      from dn_cte
        inner join jt_cte on Jt_cte.TRANSACTION_ID = dn_cte.TRANSACTION_ID
        inner join dnjnt_cte on dnjnt_cte.TRANSACTION_ID = dn_cte.TRANSACTION_ID
        inner join Jtjnt_cte on Jtjnt_cte.TRANSACTION_ID = dn_cte.TRANSACTION_ID
     )
   select TRANSACTION_ID
    , (case when (BothFound + JointG) = 2 then 1 else 0 end) as Final
   from JtCkNotCk
   order by 1
   )
go

And would be used like so:

select Id, Final
from dbo.fn_JointCreNotCk(@din,@jin) jcnc

---

Reference:

• When is a SQL function not a function? "If it’s not inline, it’s rubbish." - Rob Farley
• Inline Scalar Functions - Itzik Ben-Gan
• Scalar functions, inlining, and performance: An entertaining title for a boring post - Adam Machanic
• TSQL User-Defined Functions: Ten Questions You Were Too Shy To Ask - Robert Sheldon