t-sql inline function

Question

hi we run 2014 enterprise. i'm writing a t-sql script that will repeat certain lines 2 or more times and was hoping to find some sort of inline function capability in t-sql.

i read the article at https://stackoverflow.com/questions/981451/can-i-create-a-one-time-use-function-in-a-script-or-stored-procedure but wasnt thrilled with the answers and kept thinking there must be a way in t-sql to write a paragraph that can be "performed/called" from the main portion of the code.

Answer 1

One possibility that may work for you is to create a stored procedure. Stored procedures can pass values back into variables in the calling code. You mentioned that you didn't want to create a User function in your database. If you don't want to create permanent objects in your database, you create a local temporary stored procedure in the same way you create a local temporary table (by giving the procedure a name that begins with a hash mark (#)). Like local temporary tables, local temporary stored procedures can only be seen by the connection that created the procedure and automatically go away when the connection is closed. Unfortunately you cannot create temporary used defined function.

As an example of a stored proc that returns values to be used in a succeeding call, the following code creates a temporary stored proc (if you want a permanent stored proc, just remove the # from the name. It creates a Fibonacci series, each call returns the value to create the next number in the series.

Create Procedure #FibSequence (@LastNumber int Output, @PriorLastNumber int Output, @Sequence varchar(max) Output) As   
Declare @NewValue int;  
Set @NewValue = @LastNumber + @PriorLastNumber;  
Set @PriorLastNumber = @LastNumber;  
Set @LastNumber = @NewValue;  
Set @Sequence = @Sequence + ',' + Cast(@NewValue As varchar(11));  
go  
  
Declare @LastNumber int = 1,   
   @PriorLastNumber int = 1,   
   @Sequence varchar(max) = '1,1';  
Exec #FibSequence @LastNumber Output, @PriorLastNumber Output, @Sequence Output;  
Exec #FibSequence @LastNumber Output, @PriorLastNumber Output, @Sequence Output;  
Exec #FibSequence @LastNumber Output, @PriorLastNumber Output, @Sequence Output;  
Exec #FibSequence @LastNumber Output, @PriorLastNumber Output, @Sequence Output;  
Exec #FibSequence @LastNumber Output, @PriorLastNumber Output, @Sequence Output;  
Exec #FibSequence @LastNumber Output, @PriorLastNumber Output, @Sequence Output;  
Select @Sequence;  

Tom

Answer 2

sorry folks , i'm showing a dumbed down sample that i hope explains more clearly what i hope to accomplish...pls ignore how silly the example is and focus on what is really being asked. i dont want to add the code as a user defined function to the db. i'd prefer to stay away from dynamic sql and i'm not even sure it can set @x1 and @x2 anyway. i need the setting of @x1 and @x2 visible to the rest of the script after the function is done doing its thing.

declare @a decimal  
declare @b decimal  
declare @c decimal  
  
declare @x1 decimal  
declare @x2 decimal  
  
line  1 of t-sql script  
line  2 of t-sql script  
line  3 of t-sql script  
line  4 of t-sql script  
line  5 of t-sql script  
line  6 of t-sql script  
line  7 of t-sql script  
line  8 of t-sql script  
line  9 of t-sql script  
line 10 of t-sql script  
  
--<--right here i need to call the quadratic formula solution inserter  
  
line  12 of t-sql script  
line  13 of t-sql script  
line  14 of t-sql script  
line  15 of t-sql script  
line  16 of t-sql script  
line  17 of t-sql script  
line  18 of t-sql script  
line  19 of t-sql script  
line  20 of t-sql script  
line  21 of t-sql script  
  
--<--right here i need to call the quadratic formula solution inserter  
  
line  23 of t-sql script  
line  24 of t-sql script  
line  25 of t-sql script  
line  26 of t-sql script  
line  27 of t-sql script  
line  28 of t-sql script  
line  29 of t-sql script  
line  30 of t-sql script  
line  31 of t-sql script  
line  32 of t-sql script  
  
return  
  
::quadaratic_formula_inserter_inline_function  
  
set @x1= -@b +....  
set @x2= -@b -....  
  
insert ...  select @x1,@x2  
  
...and more functionality i'd prefer not to repeat (for readability purposes) in the core statements of the script  



  
  
  
  

Answer 3

You could structure the code something like this:

SET @section = 'first'
OnTop:
   IF @section = 'first'
   BEGIN 
       ---
      SET @section = 'second'
     GOTO MyLocalFunction
   END
   ELSE IF @section = 'second'
   BEGIN 
       ---
      SET @section = 'third'
     GOTO MyLocalFunction
   END
   ...
   RETURN

MyLocalFunction:
   SET @x1 = ...
   SET @x2  ...
   GOTO OnTop

But to don't be surprised to see a few raised eyebrows when you present this to your colleagues.

This is partly drawn from the system I mainly work with these days. We have many stored procedures that are APIs that which have several actions the perform different task - and one action can initiate another one. However, we stay away from GOTO, and we rather make recursive call in such cases.

Answer 4

according to this article you cant use output with a function https://www.sqlservercentral.com/forums/topic/how-to-set-parameter-in-function-as-output .

i suppose i could call a proc that does the repetitive logic. and use its output items to set the script vars appropriately.

my script is so verbose that it is difficult to see the forest (algorithm) from the trees.