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


Temporary Functions?


Temporary Functions?

Author
Message
Rob-350472
Rob-350472
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1481 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 Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1481 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-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34334 Visits: 11359
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

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

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

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

Group: General Forum Members
Points: 1481 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-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31172 Visits: 8670
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-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

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

Group: General Forum Members
Points: 18995 Visits: 7410
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