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


Temporary Functions?


Temporary Functions?

Author
Message
Rob-350472
Rob-350472
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

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

Group: General Forum Members
Points: 139 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
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16256 Visits: 11355
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
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 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
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

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

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

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

Group: General Forum Members
Points: 12865 Visits: 8565
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
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 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
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8136 Visits: 7163
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)[size=2]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.[/size]
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