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 ..
Best Answer
To replicate this kind of code, without creating a function:
You can use
CROSS APPLY
: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.
Or you could ask for the permissions to create your function somewhere.