Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temporary Functions?


Temporary Functions?

Author
Message
Rob-350472
Rob-350472
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 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-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 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
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

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

can u share FOR XML PATH and STUFF sub query method for solving temp function issues.?
Rob-350472
Rob-350472
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5957 Visits: 8312
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 at GMail
Gregory Hart
Gregory Hart
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3944 Visits: 6680
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