question

db042190-2983 avatar image
1 Vote"
db042190-2983 asked ErlandSommarskog commented

t-sql inline function

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.

sql-server-transact-sql
· 12
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't understand what you mean; can you give us an example, please?

1 Vote 1 ·

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.

Are you looking for sql functions that can repeat certain rows two or more times?

can be "performed/called" from the main portion of the code.

What does it mean?


[1]: /answers/storage/attachments/106098-image.png
[2]: /answers/storage/attachments/106133-image.png


1 Vote 1 ·
image.png (21.5 KiB)
image.png (7.3 KiB)

no echoliu. i have a block of code functionality that i dont want to repeat in the script every time i need to do the same thing. its like calling a function multiple times but i dont want to add the function to the db.

0 Votes 0 ·

I think I understand your request - and that isn't possible. You cannot create a function inside a stored procedure - that is private to that procedure and only available in that code. You either need to create an actual function - or you need to repeat the code each time.

Why are you set against creating a function? If the issue is performance - then create the function as an inline-table valued function, which will then be incorporated into the calling query and optimized.

0 Votes 0 ·
Show more comments

It isn't clear what you are asking - but I think you may be looking for CROSS/OUTER APPLY. You can write code as a query in an APPLY that is executed for each row from the outer query.

0 Votes 0 ·

thx jeffrey. no i didnt ask the question clearly. i want to call the same function from multiple places in my script without adding the function to the db. and i want to keep the number of lines involved in that call to a minimum which means it would be nice if the function can set variables needed by the rest of the script.

0 Votes 0 ·

@db042190-2983,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered TomCooper-6989 published

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered JeffreyWilliams-3310 commented

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



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you provide the formula - and a real example of how you want to call it? I still think you need to look at CROSS APPLY - as that provides the functionality you appear to be asking about.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

wild but probably not for me. thx

0 Votes 0 ·

wild but probably not for me. thx

'
I draw a sigh of relief. :-)

Then again, I think my suggestion is closest to the very vague outline you have given.

But as Jeffery said, if you don't give us enough information about your problem, we can't help you.

One more alternative: if this is a script, and the code is recurring, you could put this recurring code in a separate file and run the script from SQLCMD. Or from SQLCMD mode in SSMS. And, yes, I can understand your objections to this idea.
0 Votes 0 ·
db042190-2983 avatar image
0 Votes"
db042190-2983 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.