September 1, 2010 at 4:06 am
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!
September 1, 2010 at 5:03 am
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.. 😛
September 1, 2010 at 5:18 am
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!
September 1, 2010 at 6:05 am
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
SQLkiwi blog
@SQL_Kiwi
September 1, 2010 at 6:08 am
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 🙂
July 25, 2013 at 12:10 am
Hi Veteran,
can u share FOR XML PATH and STUFF sub query method for solving temp function issues.?
July 25, 2013 at 2:56 am
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!
July 28, 2013 at 7:57 am
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
August 29, 2016 at 2:56 pm
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.
August 31, 2016 at 9:48 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 31, 2016 at 10:22 am
ScottPletcher (8/31/2016)
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.
It actually makes a temporary procedure. I'm not sure if there's a real need for these type of procedures, but they do exist.
Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. Temporary names cannot be specified for CLR procedures.The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. The complete name for a local temporary procedure, including #, cannot exceed 116 characters.
August 31, 2016 at 8:52 pm
Luis Cazares (8/31/2016)
It actually makes a temporary procedure. I'm not sure if there's a real need for these type of procedures,
I can think of one - fighting parameter sniffing issue.
Normally dynamic query is enough for that purpose, but if there is more than 1 query involved, and there are repeated calls involved - that might be useful.
_____________
Code for TallyGenerator
September 1, 2016 at 12:06 pm
]I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again.
This is not how we intended SQL to be used. :w00t:Functions cannot be used by the optimizer, hide code in an attempt by non-SQL programmers to make their code look like whatever that their familiar first procedural language was. Jeff Modem recently posted a piece on his use of CLR functions; his remark was that he writes -1 per month; no, that is not a mistake he goes back and removes them and uses pure SQL instead. This is for performance and maintainability.
CREATE FUNCTION [dbo].[f_functionname](parameters)
You are probably not old enough to remember it, but in the first versions of Fortran, we had to prefix in-line functions with "FN_" because the compilers were very simple one pass things. This is why the T-SQL dialect still uses @,@@, #, and ## today. In data modeling. We call this design flaw a Tibble from the practice of putting "TBL_" on table names (we also have the Volkswagen "VW_"), and it has been the subject of several humor pieces.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 1, 2016 at 12:35 pm
CELKO (9/1/2016)
]I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again.
This is not how we intended SQL to be used. :w00t:Functions cannot be used by the optimizer, hide code in an attempt by non-SQL programmers to make their code look like whatever that their familiar first procedural language was. Jeff Modem recently posted a piece on his use of CLR functions; his remark was that he writes -1 per month; no, that is not a mistake he goes back and removes them and uses pure SQL instead. This is for performance and maintainability.
CREATE FUNCTION [dbo].[f_functionname](parameters)
You are probably not old enough to remember it, but in the first versions of Fortran, we had to prefix in-line functions with "FN_" because the compilers were very simple one pass things. This is why the T-SQL dialect still uses @,@@, #, and ## today. In data modeling. We call this design flaw a Tibble from the practice of putting "TBL_" on table names (we also have the Volkswagen "VW_"), and it has been the subject of several humor pieces.
No, functions are used so that its code can be re-used. Seriously, your obsession with labeling everyone a Luddite gets insufferable.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 1, 2016 at 1:42 pm
ScottPletcher (9/1/2016)
CELKO (9/1/2016)
]I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again.
This is not how we intended SQL to be used. :w00t:Functions cannot be used by the optimizer, hide code in an attempt by non-SQL programmers to make their code look like whatever that their familiar first procedural language was. Jeff Modem recently posted a piece on his use of CLR functions; his remark was that he writes -1 per month; no, that is not a mistake he goes back and removes them and uses pure SQL instead. This is for performance and maintainability.
CREATE FUNCTION [dbo].[f_functionname](parameters)
You are probably not old enough to remember it, but in the first versions of Fortran, we had to prefix in-line functions with "FN_" because the compilers were very simple one pass things. This is why the T-SQL dialect still uses @,@@, #, and ## today. In data modeling. We call this design flaw a Tibble from the practice of putting "TBL_" on table names (we also have the Volkswagen "VW_"), and it has been the subject of several humor pieces.
No, functions are used so that its code can be re-used. Seriously, your obsession with labeling everyone a Luddite gets insufferable.
And there are times when a CLR function may actually be the right tool for a given job or task, just like a cursor could be the right tool for a given job or task.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply