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.
This question is related to
sql
sql-server
sql-server-2005
tsql
scripting
In scripts you have more options and a better shot at rational decomposition. Look into SQLCMD mode (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.
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'
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.
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 ..
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.
Source: Stackoverflow.com