How can I include a Temp Table in a SQL Function

Question

I have a SQL Server 2014 server linked to an Oracle server. I want to use a temp table in a function to return a dataset from the Oracle database and then use the my function to return results using regular T-SQL. Since I am rather new to this I am close but am getting an error message

Msg 156, Level 15, State 1, Procedure GetBond, Line 37
Incorrect syntax near the keyword 'BEGIN'.

I have posted the function code here:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetBond]
    (@WarControlID bigint)
RETURNS VARCHAR(MAX)
AS
BEGIN
    --Create Temp Table
    declare @TSQL varchar(max)

    DECLARE @WarrantBail table 
    (
         WR_INVL varchar(5),
         WR_WARR_CTL VarChar(10),
         WR_Bail VarChar(50),
         WC_BAIL VarChar(50)
    )

    SELECT  @TSQL = 'SELECT * FROM OPENQUERY(RMSPROD2,''SELECT TIBURON.WRMAST.WR_INVL, TIBURON.WRMAST.WR_WARR_CTL,TIBURON.WRMAST.WR_BAIL,TIBURON.WRWCHG.WC_BAIL
                     FROM TIBURON.WRMAST 
                     LEFT JOIN TIBURON.WRWCHG ON WRWCHG.WC_WR_CHAIN = WRMAST.WRMAST_ROW
                     WHERE TIBURON.WRMAST.WR_WARR_CTL = ''''' + @WarControlID + ''''''')'

    INSERT INTO @WarrantBail
       EXEC (@TSQL)
END

BEGIN    
    -- Create a Variable
    DECLARE @NoBailCount int
    DECLARE @ChgCount int
    DECLARE @WarTotalBond float
    DECLARE @CHGTotalBond float
    DECLARE @War_Final_Bail varchar(max)

    Select COUNT(DISTINCT w.WR_Bail) AS NoBond_Count
        From @WarrantBail w
        Where w.WC_BAIL In ('No Bond', 'No Bail','None') Or w.WR_Bail In ('No Bond', 'No Bail','None')

--***********Get Charge Count
        Select COUNT(w.WC_BAIL) As ChgCount FROM @WarrantBail w

        --******************IF the above fails then we have a bond check the Warrant bond amount
        Select SUM (DISTINCT cast(w.WR_Bail As int)) AS WAR_Bond_Total
                From @WarrantBail w
                Where w.WR_Bail Not In ('No Bond', 'No Bail','None')

--****************We may have additional charges get the total for those charges
        Select SUM (cast(w.WC_BAIL As int)) AS CHG_BondTotal
                From @WarrantBail w
                Where w.WC_BAIL Not In ('No Bond', 'No Bail','None')

    IF (@NoBailCount > 0)
        Begin

            SET @War_Final_Bail = 'NO BAIL'
        End
    ELSE IF @ChgCount > 0
        Begin   
            SET @War_Final_Bail = @WarTotalBond + @CHGTotalBond
        End
    Else
        Begin
        SET @War_Final_Bail = @WarTotalBond
        End
    RETURN CONVERT(varchar(max), @War_Final_Bail)

END

In addition to the Error when I Execute the code I am also seeing a squiggly line under the Line "ALTER FUNCTION [dbo].[GetBond]

That error states:

Incorrect syntax: 'ALTER FUNCTION' must be the only statement in this batch.

Does this error mean I cannot create a temp table in the function?

You can't execute dynamic sql in a function. You would have to convert this to a stored procedure. msdn.microsoft.com/en-us/library/ms191320.aspx — Sean Lange
– Sean Lange, Commented Apr 21, 2016 at 19:08

BenM · Accepted Answer · 2016-04-21 19:14:41Z

0

Why do you have an END and BEGIN here? Think this is likely (one of) your problem(s).

Insert Into @WarrantBail
       EXEC (@TSQL)
END

BEGIN    
    -- Create a Variable
    DECLARE @NoBailCount int

Do you absolutely have to use dynamic SQL? Why not do something like this...

INSERT INTO @WarrantBail
SELECT * 
FROM OPENQUERY(RMSPROD2, '
    SELECT TIBURON.WRMAST.WR_INVL,  
    TIBURON.WRMAST.WR_WARR_CTL,
    TIBURON.WRMAST.WR_BAIL,
    TIBURON.WRWCHG.WC_BAIL
    FROM TIBURON.WRMAST 
        LEFT JOIN TIBURON.WRWCHG ON WRWCHG.WC_WR_CHAIN = WRMAST.WRMAST_ROW
    WHERE TIBURON.WRMAST.WR_WARR_CTL = ' + CAST(@WarControlID AS VARCHAR(30)) + ')')

edited Apr 21, 2016 at 19:14

answered Apr 21, 2016 at 19:09

BenM

1045 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Perry · Accepted Answer · 2016-04-23 23:46:41Z

0

Thanks for your comments I am a junior developer and am just getting my first taste of SQL programming after a discussion with one of the senior developers I was able to complete the task by using a Stored Procedure.

Thank you for your comments.

answered Apr 23, 2016 at 23:46

Perry

1,3573 gold badges20 silver badges42 bronze badges

Collectives™ on Stack Overflow

How can I include a Temp Table in a SQL Function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related