Can I create a One-Time-Use Function in a Script or Stored Procedure?
SqlSql ServerSql Server-2005TsqlScriptingSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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 ..
Solution 2 - Sql
You can call CREATE Function
near the beginning of your script and DROP Function
near the end.
Solution 3 - Sql
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.
Solution 4 - Sql
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'
Solution 5 - Sql
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.
Solution 6 - Sql
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.