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 ..

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.

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