155

In SQL Server 2005, is there a concept of a one-time-use, or local function declared inside of a SQL script or Stored Procedure? I'd like to abstract away some complexity in a script I'm writing, but it would require being able to declare a function.

Just curious.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Mark Carpenter
  • 17,445
  • 22
  • 96
  • 149
  • there's probably a better way to do what you want without a function. perhaps you should post a snippet of the code you're wanting to turn into a function? – DForck42 Jun 11 '09 at 16:09
  • are you generating a function dynamically so it is different each time? if you function is always the same just leave it in the database – KM. Jun 11 '09 at 18:09
  • 3
    I was trying to do it as a way to let the query more readable. The idea of creating huge queries makes hard to maintain. – Jp_ Nov 07 '17 at 19:25

7 Answers7

140

You can create temp stored procedures like:

create procedure #mytemp as
begin
   select getdate() into #mytemptable;
end

in an SQL script, but not functions. You could have the proc store it's result in a temp table though, then use that information later in the script ..

Ron Savage
  • 10,923
  • 4
  • 26
  • 35
  • 12
    This should be the answer. This is truly single-use if only connection scoped temporary (single #), and has the benefit of circumventing sql user restrictions. – Kind Contributor Jul 06 '16 at 04:07
  • How is it used then? Isn't it a typo in the procedure name used in the select into expression? – jgomo3 Sep 08 '16 at 13:46
  • 1
    I'm able to get results from your example stored procedure when I remove the `BEGIN` keyword, and replace the `END` keyword with `GO`. – Joseph Dykstra Nov 10 '17 at 18:13
  • The OP was asking for a temporary FUNCTION and at least SQL server 2012 won't allow the #-syntax for functions. Only procedures. – Erk Jun 05 '18 at 12:52
  • That is not function within a script and may still require permissions. In order to avoid repetitive segments the only option SQL has is WITH statement. –  Dec 06 '19 at 17:38
  • select getdate() as myColumnName into #mytemptable; – Scott Howard Mar 08 '23 at 20:00
87

You can call CREATE Function near the beginning of your script and DROP Function near the end.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • 8
    I was going to suggest this. Just be careful that your script finishes; if it aborts, you'll still have the function in the DB. – chocojosh Jun 11 '09 at 14:34
  • 7
    You can do a IF EXISTS check before each run and delete if anything is found. – Adrian Godong Jun 11 '09 at 14:36
  • 7
    @chocojosh, that should be ok if you wrap it in a transaction. The function shouldn't be in the database if the transaction bombs. – Jeff LaFay Apr 25 '13 at 21:16
  • 22
    @JoelCoehoorn : this does still require write privileges. – user2284570 May 26 '15 at 13:47
  • 2
    Note that this won't work inside of a function - temporary functions inside of functions aren't allowed. See: https://technet.microsoft.com/en-us/library/ms191320.aspx#Restrictions – Daniel Neel Dec 23 '15 at 20:17
  • 1
    What if there are multiple people using the same stored procedure almost at the same time? (like a high traffic website) then suddenly the function gets deleted when the other store procedure was using it, I think we need to consider this scenario before create and delete unless the name of the function is dynamic this could bring a lot of issues. – Rolando Retana Oct 11 '16 at 23:17
  • @RolandoRetana I don't think it should with proper use of transactions. – Oskar Berggren Sep 13 '17 at 16:43
  • Don't do this. Create a function and leave it there if you really need a function. If you want a scripted function use WITH statement. –  Dec 06 '19 at 17:39
27

Common Table Expressions let you define what are essentially views that last only within the scope of your select, insert, update and delete statements. Depending on what you need to do they can be terribly useful.

Welbog
  • 59,154
  • 9
  • 110
  • 123
  • 5
    This should be accepted as correct answer. The accepted answer is not thread safe. – kalyan Oct 02 '14 at 20:31
  • 11
    Depends what you're trying to do. I found this question because I'm writing a data seeder and I don't want to repeat 10 lines of MERGE INTO 30 times. I don't care about threadsafe and CTEs won't work for me. – solipsicle Oct 21 '14 at 16:11
  • 27
    I think this answer, and the assertions that it's the correct answer, miss that the question is looking for a temp FUNCTION, not temp TABLE. Unless I'm missing something (not uncommon) CTEs are comparable to temp tables. – JD Long Jun 01 '15 at 15:17
  • 13
    A function can take arguments while a CTE can not. – Răzvan Flavius Panda May 18 '16 at 12:05
  • 4
    There are many differences between a CTE and a temp stored procedure (which is the correct answer here IMO). For starters, CTEs only exist for a single statement, whereas temp variables can be used throughout a script. Other differences include: (1) CTEs cannot house the same logic that an SP can, (2) CTEs cannot accept variables. A CTE is just syntactic sugar to allow you to more easily build nested table expressions for use in a statement. Even then they can be dangerous performance-wise if you aren't aware of the caveats. – Ross Brasseaux Feb 22 '19 at 22:14
17

I know I might get criticized for suggesting dynamic SQL, but sometimes it's a good solution. Just make sure you understand the security implications before you consider this.

DECLARE @add_a_b_func nvarchar(4000) = N'SELECT @c = @a + @b;';
DECLARE @add_a_b_parm nvarchar(500) = N'@a int, @b int, @c int OUTPUT';

DECLARE @result int;
EXEC sp_executesql @add_a_b_func, @add_a_b_parm, 2, 3, @c = @result OUTPUT;
PRINT CONVERT(varchar, @result); -- prints '5'
Tmdean
  • 9,108
  • 43
  • 51
4

The below is what I have used i the past to accomplish the need for a Scalar UDF in MS SQL:

IF OBJECT_ID('tempdb..##fn_Divide') IS NOT NULL DROP PROCEDURE ##fn_Divide
GO
CREATE PROCEDURE ##fn_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
GO

Exec ##fn_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.

Gregory Hart
  • 137
  • 1
  • 7
  • 2
    Can someone please tell me what the difference to this answer https://stackoverflow.com/a/981491/161979 is? – mzuther Jan 28 '22 at 11:32
  • 1
    @mzuther The solution mentioned above is global temporary stored procedure which can be accessed from all sessions. The solution mentioned in your link is local temporary stored procedure which accessed in same session only. For more info refer https://stackoverflow.com/questions/21011276/difference-between-temptable-and-temptable – Muthu Oct 15 '22 at 17:58
3

In scripts you have more options and a better shot at rational decomposition. Look into SQLCMD mode (SSMS -> Query Menu -> SQLCMD mode), specifically the :setvar and :r commands.

Within a stored procedure your options are very limited. You can't create define a function directly with the body of a procedure. The best you can do is something like this, with dynamic SQL:

create proc DoStuff
as begin

  declare @sql nvarchar(max)

  /*
  define function here, within a string
  note the underscore prefix, a good convention for user-defined temporary objects
  */
  set @sql = '
    create function dbo._object_name_twopart (@object_id int)
    returns nvarchar(517) as
    begin
      return 
        quotename(object_schema_name(@object_id))+N''.''+
        quotename(object_name(@object_id))
    end
  '

  /*
  create the function by executing the string, with a conditional object drop upfront
  */
  if object_id('dbo._object_name_twopart') is not null drop function _object_name_twopart
  exec (@sql)

  /*
  use the function in a query
  */
  select object_id, dbo._object_name_twopart(object_id) 
  from sys.objects
  where type = 'U'

  /*
  clean up
  */
  drop function _object_name_twopart

end
go

This approximates a global temporary function, if such a thing existed. It's still visible to other users. You could append the @@SPID of your connection to uniqueify the name, but that would then require the rest of the procedure to use dynamic SQL too.

Peter Radocchia
  • 10,710
  • 2
  • 34
  • 56
  • Please update the answer to include the application name (I guess you mean SSMS). Giving a menu item without specifying the application is not very helpful. Thanks! – mzuther Jan 28 '22 at 11:30
0

Just another idea for anyone that's looking this up now. You could always create a permanent function in tempdb. That function would not be prefixed with ## or # to indicate it's a temporary object. It would persist "permanently" until it's dropped or the server is restarted and tempdb is rebuilt without it. The key is that it would eventually disappear once the server is restarted if your own garbage collection fails.

The scope of the function would be within TempDB but it could reference another database on the server with 3 part names. (dbname.schema.objectname) or better yet you can pass in all the parameters that the function needs to do its work so it doesn't need to look at other objects in other databases.

EricI
  • 3,636
  • 1
  • 16
  • 8