SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temporary Functions?


Temporary Functions?

Author
Message
Rob-350472
Rob-350472
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 684
I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again.

In the same way that temporary tables can be created can temporary functions be created? - i.e. you specify the code to create it, query using it then when you close the window it's gone. It doesn't clutter up the DB this way...

If so, how would one alter the existing syntax:

CREATE FUNCTION [dbo].[f_functionname](paramaters)

Cheers!
shield_21
shield_21
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 396
Hi there,

I don't know if that is possible.. But, why not drop the function after its use..

DROP FUNCTION [dbo].[f_functionname]
GO

I hope this helps.. :-P
Rob-350472
Rob-350472
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 684
I'd resided myself to this idea, however, a colleague introduced me to a new technique of using FOR XML PATH and STUFF as a sub query around my main select. Which seems to have done the job.

I'll need to play with it some more and look into it, atm it looks like witchcraft! lol

Cheers!
Paul White
Paul White
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80430 Visits: 11400
You can't create temporary functions, no.
It sounds as if you are writing code to concatenate strings, perhaps to create a CSV-style output?
There is an excellent performance comparison of the main methods here:
http://florianreischl.blogspot.com/2010/01/concatenation-of-text-and-binary-data.html

That link also includes a couple of links to clear explanations of the FOR XML PATH method.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Rob-350472
Rob-350472
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 684
Brilliant, thanks Paul. You're correct, I am creating comma delimited strings. Initially using functions and coalesce, now giving this FOR XML thing a whirl. I'll check out the article Smile
kgper 15790
kgper 15790
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 4
Hi Veteran,

can u share FOR XML PATH and STUFF sub query method for solving temp function issues.?
Rob-350472
Rob-350472
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 684
It seems more popular to do this with a CTE now, however, I tend to use a mix of the CTE and FOR XML approach sometimes, here's a FOR XML one which works well as a sub query.

REPLACE
(
STUFF
(
(
SELECT distinct ', '+ColumnYouWantToString
FROM TableName with (nolock)

WHERE
--Link back to outer query here.
FOR XML Path ('')
)
,1,2,''
)
,'&', '&'
) as CommaString


The CTE type thing I use:

WITH CTE AS
(
SELECT DISTINCT ID
FROM tablenamehere
WHERE in (1,2,3,n)
)

SELECT
ID,
CommaString = LTRIM(STUFF((
SELECT ', ' + ColumnYouWantToString
FROM tablenameforColumn c

WHERE c.ID = CTE.ID
ORDER BY f.ColumnYouWantToString
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
)
FROM CTE
ORDER BY ID;


Neither may be the best approach but they work for me! - I tend to use the second more now as it's tidier with the TYPE stuff at the end - saves all the replaces for & and stuff like that.

Hope that helps!
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77509 Visits: 8875
1) Please don't post to 3 year old threads. Start new ones.

2) See here for lots of information about string splitting. Note that the best method is CLR, but the delimited 8K split is a close second. Also, IIRC, XML takes a VERY large memory grant as a major drawback to that method.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Gregory Hart
Gregory Hart
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 48
The below is what I have used in the past to accomplish the need for a Scalar UDF in MS SQL:


IF OBJECT_ID('tempdb..##fn_CORP_Divide') IS NOT NULL DROP PROCEDURE ##fn_CORP_Divide;

CREATE PROCEDURE ##fn_CORP_Divide (@Numerator Real, @Denominator Real) AS
BEGIN
SELECT Division =
CASE WHEN @Denominator != 0 AND @Denominator is NOT NULL AND @Numerator != 0 AND @Numerator is NOT NULL THEN
@Numerator / @Denominator
ELSE
0
END
RETURN
END;

EXEC ##fn_CORP_Divide 6,4



This approach which uses a global variable for the PROCEDURE allows you to make use of the function not only in your scripts, but also in your Dynamic SQL needs.
ScottPletcher
ScottPletcher
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46945 Visits: 8054
Gregory Hart (8/29/2016)
The below is what I have used in the past to accomplish the need for a Scalar UDF in MS SQL:


IF OBJECT_ID('tempdb..##fn_CORP_Divide') IS NOT NULL DROP PROCEDURE ##fn_CORP_Divide;

CREATE PROCEDURE ##fn_CORP_Divide (@Numerator Real, @Denominator Real) AS
BEGIN
SELECT Division =
CASE WHEN @Denominator != 0 AND @Denominator is NOT NULL AND @Numerator != 0 AND @Numerator is NOT NULL THEN
@Numerator / @Denominator
ELSE
0
END
RETURN
END;

EXEC ##fn_CORP_Divide 6,4



This approach which uses a global variable for the PROCEDURE allows you to make use of the function not only in your scripts, but also in your Dynamic SQL needs.



That doesn't use a "global variable". It creates a proc name that starts with ##. Only table names like that become global temp tables. The proc name is just an (unusual) proc name. You can create procs in any db named ##whatever if you like.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search