How to Create a One-Time-Use Function in a Script or Stored Procedure

Can I create a One-Time-Use Function in a Script or Stored Procedure?

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

Use of temporary functions or procedure within a script

Yes you can:

CREATE PROCEDURE #usp_TempOne 
@Input INT,
@Output INT OUTPUT
as 
SET @Output = @Input * 2
RETURN
GO
DECLARE @i INT = 10, @o INT;
EXEC #usp_TempOne @i, @o OUTPUT
SELECT @o

Temporary function or stored procedure in T-SQL

Re your edit - it sounds like you should be using sp_ExecuteSQL against a (parameterized) nvarchar that contains TSQL.

Search on sp_ExecuteSQL; a simple example:

DECLARE @SQL nvarchar(4000),
 @Table varchar(20) = 'ORDERS',
 @IDColumn varchar(20) = 'OrderID',
 @ID int = 10248

 SET @SQL = 'SELECT * FROM [' + @Table + '] WHERE ['
  + @IDColumn + '] = @Key'

  EXEC sp_executesql @SQL, N'@Key int', @ID

Note that table and column names must be concatenated into the query, but values (such as @Key) can be parameterized.

---

There is a temporary stored procedure - but it is per connection, not per sp.

However, you might want to look at Common Table Expressions - they may be what you are after (although you can only read from them once).

Maybe if you can clarify what you are trying to do?

Is it possible to define a local function in a TSQL query?

No, there is no way -- create/drop is the only choice.

Function vs. Stored Procedure in SQL Server

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).

A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.

A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters
(the arguments). Functions are not allowed to change anything, must
have at least one parameter, and they must return a value. Stored
procs do not have to have a parameter, can change database objects,
and do not have to return a value.

T-SQL: How Do I Create A Private Function Inside A Stored Procedure

I first tried to create another, temporary SP, from within an existing SP - which didn't work, but after experimenting a bit I think you could go with something like this (if you don't mind dynamic SQL):

CREATE PROCEDURE sp_myTest_v1_0(@firstName NVARCHAR(255)) AS
BEGIN
    -- declare private method
    DECLARE @privateMethod NVARCHAR(255), @privateMethodSig NVARCHAR(255)
    SELECT @privateMethod = 
        'DECLARE @x INT' + CHAR(10) +
        'WHILE ISNULL(@x,0) < 10 BEGIN' + CHAR(10) +
            'PRINT @param1 + CAST(@x AS VARCHAR)' + CHAR(10) +
            'SET @x = ISNULL(@x,0)+1' + CHAR(10) +
        'END', @privateMethodSig = '@param1 NVARCHAR(255)'

    -- call privateMethod
    EXEC sp_executesql @privateMethod, @privateMethodSig, @param1 = @firstName
END
GO

---

Related Topics