How to Have Temp Tables in a Function

What can I use instead of #Temp table in sql function

Instead of temp table you can use a table variable.

declare @Temp TABLE (ID int identity, ProductId int)

insert into @Temp(ProductId)
select ProductId 
from OrderProduct
where OrderId = @OrderId

But you could rewrite your function without a loop.

Something like this should do what you want.

create function IF_GetOrderProducts
(
  @OrderId int
)
returns varchar(500)
as
begin
  return
    (
    select Name+'<br/>'
    from Product as P
      inner join OrderProduct as OP
        on P.ProductId = OP.ProductId
    where OP.OrderId = @OrderId
    for xml path(''), type
    ).value('.', 'varchar(500)')
end

Is it possible to have temp tables in a function?

No, per this thread where the same question was asked, you cannot, but you can use a table variable

DECLARE @MyTempTableVariable TABLE (SCHEMA)

INSERT INTO @MyTempTableVariable
SELECT bleh
FROM bleh

Use a temp table in a Table-valued Functions

You can use a table variable instead.

DECLARE @Child TABLE (Id INT, Name VARCHAR(255), ParentId INT, ParentName VARCHAR(255))
DECLARE @Parent TABLE (Id INT, Name VARCHAR(255), ParentId INT, ParentName VARCHAR(255))

SQL using #table/ Temp table inside a table valued function

You cannot use a Temp table in UDF because

the object can't be created inside UDF.

Instead of using a Temp table, use a table variable, that should solve your problem

CREATE FUNCTION WeekNumbersWithinRange
(   
    @FromDate DATETIME,
    @ToDate DATETIME
)
RETURNS TABLE 
AS
BEGIN

DECLARE @WeeksofRange TABLE 
(
WeekNo  INT
)

-- Get all week numbers for the given delivery date range

declare @Calendar table
(CalendarDate Date Primary key, IsWeekend Bit, YearNo SmallInt, QuarterNo TinyInt, MonthNo TinyInt, DayOfYearNo SmallInt, DayNo TinyInt, WeekNo TinyInt, WeekDayNo TinyInt )

Declare @beginDate Date, @endDate Date

Select @beginDate = @FromDate , @endDate = @ToDate

While @beginDate <= @endDate 
Begin
 Insert Into @Calendar (CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo)

 Select
  @beginDate As CalendarDate  
  ,(Case When DATEPART(Weekday, @beginDate) In (7, 1) Then 1 Else 0 End) As IsWeekend 
  ,DATEPART(Year, @beginDate) As YearNo 
  ,DATEPART(QUARTER, @beginDate) As QuarterNo 
  ,DATEPART(MONTH, @beginDate) As MonthNo 
  ,DATEPART(DayOfYear, @beginDate) As DayOfYearNo 
  ,DATEPART(Day, @beginDate) As DayNo   
  ,DATEPART(Week, @beginDate) As WeekNo
  ,DATEPART(WEEKDAY, @beginDate) As WeekDayNo 
  --,(Case When @beginDate < '02/03/2011' Then 0 Else DATEPART(Week, @beginDate) - 5 End) As mySpecificWeekNo
 Set @beginDate = DateAdd(Day, 1, @beginDate)
End

INSERT INTO @calendar
SELECT DISTINCT WeekNo FROM @Calendar;

-- End of Select all week numbers in the range

SELECT * FROM @WeeksofRange

GO

Create, drop and insert a temp table in a user-defined function

That is correct - you cannot have side effecting statements:

From here: http://msdn.microsoft.com/en-us/library/aa175085(v=sql.80).aspx

The statements in a BEGIN...END block cannot have any side effects.
Function side effects are any permanent changes to the state of a
resource that has a scope outside the function such as a modification
to a database table. The only changes that can be made by the
statements in the function are changes to objects local to the
function, such as local cursors or variables. Modifications to
database tables, operations on cursors that are not local to the
function, sending e-mail, attempting a catalog modification, and
generating a result set that is returned to the user are examples of
actions that cannot be performed in a function.

What you would find, even without your DROP statement, is that any attempt to access a temp table will give you the message (such as a SELECT ... INTO #TMP):

Cannot access temporary tables from within a function

As @Dems points out, you can use table variables. Because these are variables, they are scoped within the function, and therefore aren't side effecting.

Your function might run as:

...

BEGIN
    DECLARE @tempTable table (id varchar(20), rows int)

    insert @tempTable
    SELECT Id, COUNT(Balance) 
    FROM Table1

    INSERT  @RT_ResultFunction 
        SELECT T1.ID,T1,NAME,T2,Balance 
        FROM    Table2 T1, 
                @tempTable T2
        WHERE T1.ID = T2.ID

    RETURN END

Not tested or anything, but you get the gist.

How can I include a Temp Table in a SQL Function

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.

How to use temp Tables in a DB2 Function

Db2-LUW up to (and including) version 11.5 has limitations on usage of DGTT (Declare Global Temporary Table) in RETURNS TABLE functions, according to the documentation.

You cannot use being atomic with declare global temporary table (i.e. that statement is not supported currently in Compound SQL(inlined) blocks).

If you use MODIFIES SQL DATA then it has limitations on Compound SQL(compiled) for table functions, it works for scalar functions.

It may be better to use SQL PL stored procedures instead of table functions in this case.

You can also circumvent some limitations if you use pipelined functions, and CGTTs can help in some cases.

DB2 Temporary Table within Function

CREATE FUNCTION (SQL scalar, table, or row) statement, v9.7:

-| (3)|
'-MODIFIES SQL DATA-----'
...
|--+-RETURN----------------------+------------------------------|
| (5) |
+-Compound SQL (compiled)-----+
'-Compound SQL (inlined)------'
...

3. Valid if RETURNS specifies a table (that is, TABLE column-list). Also valid if RETURNS specifies a scalar result and the
SQL-function-body is a compound SQL (compiled) statement.

...

5. The compound SQL (compiled) statement is supported only for an SQL-function-body in an SQL scalar function definition. It is not
supported for SQL table function definitions.

CREATE FUNCTION (SQL scalar, table, or row) statement, v11.1:

-| (4) |
'-MODIFIES SQL DATA-----'
...

4. Valid only for compiled scalar function definition and an inlined table function definition.

You can't use MODIFIED SQL DATA with Compound SQL (compiled) (BEGIN ... END) for a table function. And DECLARE GTT statement is not supported by Compound SQL (inlined) (BEGIN ATOMIC ... END).

So, rewrite your table function not using Declared GTTs & INSERTs. Or try Created GTT, but create them beforehand (not in the function body).

As for your case

You may use a single SELECT statement with Common Table Expression:

CREATE FUNCTION ...
...
RETURN
WITH 
SESSION_ALLREFERRALS AS 
(
SELECT
    REF.AL_NO AS AL_NO,        
    ENQ.E_KEY AS R_KEY,
    APP.A_2ND_NAME AS R_TRADING_NAME,
    CASE WHEN DOC1.DT_NAME is null THEN 'NONE' ELSE DOC1.DT_NAME END AS R_DROP1,
    REF.AL_DATE AS R_DATE,
    CASE WHEN DOC2.DT_NAME is null THEN 'Incomplete' ELSE DOC2.DT_NAME END AS R_DROP2,
    CAST(ENQ2.E_TOOLCOMM1 AS VARCHAR(48)) AS R_COMM1
FROM
    F_ENQUIRY ENQ INNER JOIN 
        F_APPLICANT_LINK REF ON REF.AL_KEY1 = ENQ.E_KEY INNER JOIN 
        F_APPLICANT APP ON APP.A_KEY = REF.AL_KEY2 AND REF.AL_TYPE1 = 2 AND REF.AL_TYPE2 = 1 LEFT JOIN 
        F_DOC_TYPES DOC1 ON DOC1.DT_NO = REF.AL_DROP1 LEFT JOIN 
        F_DOC_TYPES DOC2 ON DOC2.DT_NO = REF.AL_DROP2 INNER JOIN 
        F_ENQUIRY ENQ2 ON ENQ2.E_KEY = REF.AL_ENQ_LINK
WHERE
    ENQ.E_PRIORITY_LINK = 204 AND   
    ENQ.E_JOB_TYPE_LINK = 0
)
, 
SESSION_REFERRAL1 AS 
(
SELECT 
    MIN(AL_NO) AL_NO, 
    R_KEY
    FROM 
    SESSION_ALLREFERRALS 
    GROUP BY R_KEY
)
,
SESSION_REFERRAL2 AS 
(
SELECT 
    MIN(AL_NO) AL_NO, 
    R_KEY 
FROM 
    SESSION_ALLREFERRALS a2
WHERE
    NOT EXISTS (SELECT 1 FROM SESSION_REFERRAL1 R1 WHERE r1.AL_NO = a2.AL_NO fetch first 1 rows only)
GROUP BY 
    R_KEY
)
,
SESSION_REFERRAL3 AS 
(
SELECT 
    MIN(AL_NO) AL_NO, 
    R_KEY 
FROM 
    SESSION_ALLREFERRALS A3
WHERE
    NOT EXISTS (SELECT 1 FROM SESSION_REFERRAL1 R1 WHERE R1.AL_NO = A3.AL_NO fetch first 1 rows only) AND 
    NOT EXISTS (SELECT 1 FROM SESSION_REFERRAL2 R2 WHERE R2.AL_NO = A3.AL_NO fetch first 1 rows only)
GROUP BY 
    R_KEY
)
SELECT 
    *
FROM
    SESSION_REFERRAL1 R1 INNER JOIN
    SESSION_ALLREFERRALS A ON A.R_KEY = R1.R_KEY AND R1.AL_NO = A.AL_NO LEFT JOIN
    SESSION_REFERRAL2 R2 ON R2.R_KEY = R1.R_KEY LEFT JOIN
    SESSION_ALLREFERRALS A2 ON A2.R_KEY = R2.R_KEY AND R2.AL_NO = A2.AL_NO LEFT JOIN 
    SESSION_REFERRAL3 R3 ON R3.R_KEY = R1.R_KEY LEFT JOIN
    SESSION_ALLREFERRALS A3 ON A3.R_KEY = R3.R_KEY AND R3.AL_NO = A3.AL_NO
;

---

Related Topics