Inline table value function sql server issue

Question

I have written an inline TVF and I have this so far. I get this error:incorrect syntax near end. Can someone suggest a workaround for this. Can i actually do it thsi way syntactically, is it correct?

CREATE FUNCTION [hsip].[Question_Dtl_Table_Id]
(   
    -- Add the parameters for the function here
    @cRegion CHAR(2) ='00',
    @cState_Code CHAR(2) = '00',
    @nFY NUMERIC(4,0) = 0,
    @nREPORT_ID NUMERIC(2,0) = 0,
    @nSECTION_ID NUMERIC(2,0) = 0,
    @nSUBSECTION_ID NUMERIC(2,0) = 0,
    @nDISPLAY_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
    @sUSER_ID VARCHAR(25) = NULL

)
RETURNS @tbl TABLE ( Region CHAR(2),
    State_Code CHAR(2),
    FY NUMERIC(4,0),
    REPORT_ID NUMERIC(2,0),
    SECTION_ID NUMERIC(2,0),
    SUBSECTION_ID NUMERIC(2,0),
    DISPLAY_NUMBER NUMERIC(38,0),
    QUESTION_NUMBER NUMERIC(38,0),
    QUESTION_PART_NUMBER NUMERIC(38,0),
    USER_ID VARCHAR(25))

AS
--RETURN 
begin

INSERT INTO @tbl


 select * from (SELECT  qi.REGION, qi.STATE_CODE, qi.FY, qi.REPORT_ID, qi.SECTION_ID, qi.SUBSECTION_ID, qi.DISPLAY_NUMBER,
    qi.QUESTION_NUMBER, qd.QUESTION_PART_NUMBER from 
    FY_ST_QUESTION_INFO qi
    inner join
    FY_ST_QUESTION_DETAIL qd
    on qi.FY_ST_QUESTION_INFO_TABLE_ID= qd.FY_ST_QUESTION_INFO_TABLE_ID
    where qi.region= @cRegion
    and qi.fy= @nFY
    and qi.REPORT_ID = @nREPORT_ID
    and qi.SECTION_ID = @nSECTION_ID
    and qi.SUBSECTION_ID = @nSUBSECTION_ID
    and qi.DISPLAY_NUMBER =@nDISPLAY_NUMBER
    and (qi.REPORTER_ID = @sUSER_ID or
    qi.DELEGATE_ID= @sUSER_ID or
    qi.SUB_DELEGATE_ID = @sUSER_ID));
return;
end

Thanks

That's not an inline-table-valued-function(ITVF) but a multi-statement table-valued function(MTVF) — Tim Schmelter
– Tim Schmelter, Commented Sep 21, 2016 at 15:13

Sean Lange · Accepted Answer · 2016-09-21 15:17:55Z

As Tim already mentioned in a comment this is not an inline table valued function but a multi-statement table valued function. The difference here is performance. The MTVF will generally perform even worse than scalar functions. However, a slight modification and this can easily be converted to ITVF.

CREATE FUNCTION [hsip].[Question_Dtl_Table_Id]
(   
    -- Add the parameters for the function here
    @cRegion CHAR(2) ='00',
    @cState_Code CHAR(2) = '00',
    @nFY NUMERIC(4,0) = 0,
    @nREPORT_ID NUMERIC(2,0) = 0,
    @nSECTION_ID NUMERIC(2,0) = 0,
    @nSUBSECTION_ID NUMERIC(2,0) = 0,
    @nDISPLAY_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
    @sUSER_ID VARCHAR(25) = NULL

) RETURNS TABLE AS RETURN 

    SELECT qi.REGION
        , qi.STATE_CODE
        , qi.FY
        , qi.REPORT_ID
        , qi.SECTION_ID
        , qi.SUBSECTION_ID
        , qi.DISPLAY_NUMBER
        , qi.QUESTION_NUMBER
        , qd.QUESTION_PART_NUMBER 
    from FY_ST_QUESTION_INFO qi
    inner join FY_ST_QUESTION_DETAIL qd on qi.FY_ST_QUESTION_INFO_TABLE_ID = qd.FY_ST_QUESTION_INFO_TABLE_ID
    where qi.region = @cRegion
        and qi.fy = @nFY
        and qi.REPORT_ID = @nREPORT_ID
        and qi.SECTION_ID = @nSECTION_ID
        and qi.SUBSECTION_ID = @nSUBSECTION_ID
        and qi.DISPLAY_NUMBER = @nDISPLAY_NUMBER
        and 
        (
            qi.REPORTER_ID = @sUSER_ID 
            or
            qi.DELEGATE_ID = @sUSER_ID 
            or
            qi.SUB_DELEGATE_ID = @sUSER_ID
        )

JamieD77 · Accepted Answer · 2016-09-21 15:14:41Z

1

You need to add an alias to your derived query..

INSERT  INTO @tbl
                SELECT  *
                FROM    (SELECT 
                        ) AS T -- alias 
    ;

answered Sep 21, 2016 at 15:14

JamieD77

14k1 gold badge19 silver badges27 bronze badges

1 Comment

user3147594 Over a year ago

Thanks. My query works now. Just had to add as alias table name before end.

Matt · Accepted Answer · 2016-09-21 15:18:30Z

your outer most select is redundant and does not have a required alias for the inner select. Instead of adding an alias I would correct by removing:

CREATE FUNCTION [hsip].[Question_Dtl_Table_Id]
(   
    -- Add the parameters for the function here
    @cRegion CHAR(2) ='00',
    @cState_Code CHAR(2) = '00',
    @nFY NUMERIC(4,0) = 0,
    @nREPORT_ID NUMERIC(2,0) = 0,
    @nSECTION_ID NUMERIC(2,0) = 0,
    @nSUBSECTION_ID NUMERIC(2,0) = 0,
    @nDISPLAY_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
    @sUSER_ID VARCHAR(25) = NULL

)
RETURNS @tbl TABLE ( Region CHAR(2),
    State_Code CHAR(2),
    FY NUMERIC(4,0),
    REPORT_ID NUMERIC(2,0),
    SECTION_ID NUMERIC(2,0),
    SUBSECTION_ID NUMERIC(2,0),
    DISPLAY_NUMBER NUMERIC(38,0),
    QUESTION_NUMBER NUMERIC(38,0),
    QUESTION_PART_NUMBER NUMERIC(38,0),
    USER_ID VARCHAR(25))

AS
--RETURN 
begin

INSERT INTO @tbl


SELECT
    qi.REGION, qi.STATE_CODE, qi.FY, qi.REPORT_ID, qi.SECTION_ID, qi.SUBSECTION_ID, qi.DISPLAY_NUMBER,
    qi.QUESTION_NUMBER, qd.QUESTION_PART_NUMBER
from 
    FY_ST_QUESTION_INFO qi
    inner join FY_ST_QUESTION_DETAIL qd
    on qi.FY_ST_QUESTION_INFO_TABLE_ID = qd.FY_ST_QUESTION_INFO_TABLE_ID
where
    qi.region= @cRegion
    and qi.fy= @nFY
    and qi.REPORT_ID = @nREPORT_ID
    and qi.SECTION_ID = @nSECTION_ID
    and qi.SUBSECTION_ID = @nSUBSECTION_ID
    and qi.DISPLAY_NUMBER =@nDISPLAY_NUMBER
    and (qi.REPORTER_ID = @sUSER_ID or
          qi.DELEGATE_ID= @sUSER_ID or
          qi.SUB_DELEGATE_ID = @sUSER_ID);
return;
end

But as Tim commented this is a Multi Statement Table Valued Function, but because you are simply doing a select you can make it In-Line like so:

CREATE FUNCTION [hsip].[Question_Dtl_Table_Id]
(   
    -- Add the parameters for the function here
    @cRegion CHAR(2) ='00',
    @cState_Code CHAR(2) = '00',
    @nFY NUMERIC(4,0) = 0,
    @nREPORT_ID NUMERIC(2,0) = 0,
    @nSECTION_ID NUMERIC(2,0) = 0,
    @nSUBSECTION_ID NUMERIC(2,0) = 0,
    @nDISPLAY_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
    @sUSER_ID VARCHAR(25) = NULL

)
RETURNS TABLE
AS
RETURN
(
SELECT
    qi.REGION, qi.STATE_CODE, qi.FY, qi.REPORT_ID, qi.SECTION_ID, qi.SUBSECTION_ID, qi.DISPLAY_NUMBER,
    qi.QUESTION_NUMBER, qd.QUESTION_PART_NUMBER
from 
    FY_ST_QUESTION_INFO qi
    inner join FY_ST_QUESTION_DETAIL qd
    on qi.FY_ST_QUESTION_INFO_TABLE_ID = qd.FY_ST_QUESTION_INFO_TABLE_ID
where
    qi.region= @cRegion
    and qi.fy= @nFY
    and qi.REPORT_ID = @nREPORT_ID
    and qi.SECTION_ID = @nSECTION_ID
    and qi.SUBSECTION_ID = @nSUBSECTION_ID
    and qi.DISPLAY_NUMBER =@nDISPLAY_NUMBER
    and (qi.REPORTER_ID = @sUSER_ID or
          qi.DELEGATE_ID= @sUSER_ID or
          qi.SUB_DELEGATE_ID = @sUSER_ID)
)
GO

user3147594 · Accepted Answer · 2016-09-21 15:24:25Z

use [HSIP]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
ALTER FUNCTION [hsip].[Question_Dtl_Table_Id]
(   

    @cRegion CHAR(2) ='00',
    @cState_Code CHAR(2) = '00',
    @nFY NUMERIC(4,0) = 0,
    @nREPORT_ID NUMERIC(2,0) = 0,
    @nSECTION_ID NUMERIC(2,0) = 0,
    @nSUBSECTION_ID NUMERIC(2,0) = 0,
    @nDISPLAY_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_NUMBER NUMERIC(38,0) = 0,
    @nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
    @sUSER_ID VARCHAR(25) = NULL

)
RETURNS @tbl TABLE ( Region CHAR(2),
    State_Code CHAR(2),
    FY NUMERIC(4,0),
    REPORT_ID NUMERIC(2,0),
    SECTION_ID NUMERIC(2,0),
    SUBSECTION_ID NUMERIC(2,0),
    DISPLAY_NUMBER NUMERIC(38,0),
    QUESTION_NUMBER NUMERIC(38,0),
    QUESTION_PART_NUMBER NUMERIC(38,0))


AS

begin

INSERT INTO @tbl


 select * from (SELECT  qi.REGION, qi.STATE_CODE, qi.FY, qi.REPORT_ID, qi.SECTION_ID, qi.SUBSECTION_ID, qi.DISPLAY_NUMBER,
    qi.QUESTION_NUMBER, qd.QUESTION_PART_NUMBER from 
    FY_ST_QUESTION_INFO qi
    inner join
    FY_ST_QUESTION_DETAIL qd
    on qi.FY_ST_QUESTION_INFO_TABLE_ID= qd.FY_ST_QUESTION_INFO_TABLE_ID
    where qi.region= @cRegion
    and qi.fy= @nFY
    and qi.REPORT_ID = @nREPORT_ID
    and qi.SECTION_ID = @nSECTION_ID
    and qi.SUBSECTION_ID = @nSUBSECTION_ID
    and qi.DISPLAY_NUMBER =@nDISPLAY_NUMBER
    and (qi.REPORTER_ID = @sUSER_ID or
    qi.DELEGATE_ID= @sUSER_ID or
    qi.SUB_DELEGATE_ID = @sUSER_ID)
    and qd.QUESTION_PART_NUMBER =@nQUESTION_PART_NUMBER) as t;

--return @tbl;
return;

END

GO

You would be much better off going with the version of this query found in Sean's answer. His version is truly a Inline TVF and will perform much better.

Collectives™ on Stack Overflow

Inline table value function sql server issue

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related