SQL server create temporary function as a CTE

Question

I need to create a temp function, for use in a bigger query but I don't have create permissions on the database (except for #TEMP tables).

Is there any way that I could use a CTE or a #temp query for this purpose. Maybe I'm missing something really simple here.

Example (of what it could look like):-

with add1(x) as
 return x+1
select add1(v.Value1), add1(v.Value2)
from Values v

Values table

Id Value1 Value2
1 1 4
2 2 5
3 3 6

---

EDIT

As per Aaron Bertrand's answer I managed to get something working.

CREATE TABLE #myTempTable
(
 id int identity(1,1) primary key, 
 amount int, 
 col1 varchar(10),
 col2 varchar(4)
);
-- quite a few more cols in my actual temp table, 
-- omitted to show the real issue
INSERT #myTempTable(amount,col1, col2) VALUES(10,'a1', 'b1'),(15,'a2','b2');
;WITH processed AS 
(
 SELECT * FROM #myTempTable AS r
 UNPIVOT (Result FOR [Value] IN r.Amount) unp
 CROSS APPLY
 ( 
 /******** COMPLEX FUNCTION HERE ********/
 /**** Applies to output of unpivot *****/
 SELECT unp.Result + 10 [Processed_amount]
 ) AS a
 --PIVOT (max(orig) FOR Value IN ( amount)) AS p2
)
select top 10 [Processed_amount], * from processed

The final PIVOT is messing up the results though. I was wondering why that is required.

I am still trying to wrap my head around the various parts of UNPIVOT and PIVOT.

---

EDIT2

Please see my answer,
I got it to work where we need only column to be processed ..

• Scott Hodgin - Retired: Your current function example doesn't do anything other than add 1 to the value passed in. You could just as easily say select value1 + 1 as Value1, value2 + 1 as value2.
• heyNow: the function I really need is much more complicated, this is just to illustrate that I want a function executed via a CTE
• Hannah Vernon: Add the actual function text, or at least give a tiny idea of what you expect. Extremely simple T-SQL is not helpful
• Aaron Bertrand: You could try creating a function in tempdb, but after the permissions issues you'll likely have the same issue you'll have in a user database, and that is what happens when two people run this code at the same time?

Answer 1

To replicate this kind of code, without creating a function:

CREATE FUNCTION dbo.add1(@x int)
RETURNS int
AS
BEGIN
 RETURN (SELECT @x + 1);
END
GO
SELECT dbo.add1(v.Value1), dbo.add1(v.Value2)
FROM (VALUES(1,2),(3,4)) AS v(Value1, Value2);

You can use CROSS APPLY:

SELECT z.v1, z.v2
FROM (VALUES(1,2),(3,4)) AS v(Value1, Value2)
CROSS APPLY
(
 SELECT v.Value1 + 1, v.Value2 + 1
) AS z(v1,v2);

If the function is extremely complicated and you don't want to repeat it, and that is the actual problem, you could try this solution. It is complex but allows you to only write the function once. If you need to expand this to more than two columns it gets more complicated.

CREATE TABLE #vals
(
 id int identity(1,1) primary key, 
 a int, 
 b int
);
INSERT #vals(a,b) VALUES(1,2),(15,16);
;WITH vals AS 
(
 SELECT * FROM #vals AS v
 UNPIVOT (Result FOR [Value] IN (a,b)) unp
 CROSS APPLY
 ( 
 /******** COMPLEX FUNCTION HERE ********/
 /**** Applies to output of unpivot *****/
 SELECT unp.Result + 10
 ) AS new(orig)
 PIVOT (MAX(orig) FOR Value IN ([a],[b])) AS p2
)
SELECT v1.id, 
 OriginalValue1 = v1.Result, 
 Value1 = v1.a, 
 OriginalValue2 = v2.Result, 
 Value2 = v2.b 
FROM vals AS v1 
JOIN vals AS v2 ON v1.id = v2.id
AND v1.a IS NOT NULL
AND v2.b IS NOT NULL;
GO
DROP TABLE #vals;

Or you could ask for the permissions to create your function somewhere.

• Aaron Bertrand: Big thanks to @AMtwo who sat beside me while I wrote that pivot/unpivot query and slapped my hand every time I typed the wrong syntax. Which was a lot.
• heyNow: Though not exactly answer, this gets me pretty close. Just need to wrap my head around these PIVOT and UNPIVOT concepts and edit my answer again.

Answer 2

I'm putting further work in an answer instead of cluttering up the actual question..

I was able to make progress thanks to Aaron Bertrand's answer, so I've marked that as the answer.
People reading this question should look at that first to get more context.

It turns out that if only one column is needed to be processed using that local function, we don't actually need UNPIVOT and PIVOT and can manage with just a CROSS APPLY

CREATE TABLE #myTempTable
(
 id int identity(1,1) primary key, 
 Amount int, 
 Col1 varchar(10),
 Col2 varchar(4)
);
-- quite a few more cols in my actual temp table, 
-- omitted to show the real issue
INSERT #myTempTable(Amount,Col1, Col2) VALUES(10,'a1', 'b1'),(15,'a2','b2');
;WITH p AS 
(
 SELECT * FROM #myTempTable AS q
 CROSS APPLY
 ( 
 /******** COMPLEX FUNCTION HERE ********/
 /**** Applies to output of unpivot *****/
 SELECT q.Amount + 10 [Processed_amount]
 ) AS r 
)
select top 10 [Processed_amount], * from p