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

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to scripting