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

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

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

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

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

[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