Table Variable in SQL Server Function from Input Columns

Question

I would like to create a function that returns a column based on input from three other columns. As temporary tables are not allowed within functions, is it possible to create a table variable from three input columns?

CREATE FUNCTION dbo.convert_value(
    @CustomerID VARCHAR(MAX),
    @CustomerValue VARCHAR(MAX),
    @CustomerDescription VARCHAR(MAX)
)
RETURNS FLOAT
AS BEGIN

DECLARE @CustomerTable TABLE (
    UniquePatientUID VARCHAR(MAX),
    ResultValue VARCHAR(MAX),
    PracticeDescription VARCHAR(MAX)
);

-- How can I insert @UniquePatientUID, @ResultValue and @PracticeDescription into @CustomerTable

END

The context of this question is that I have a SQL script that uses temporary tables and many UPDATE and ALTER TABLE statements, that I need to convert into a function. That script begins with the three columns mentioned, and adds a fourth column, Converted_Value, which is calculated with several hundred lines of code and manipulating temporary tables. Is there any hope here?

Inserting into a table variable is no different from any other table. But what is it you want to do once the rows are in your table variable? If you just want to return all three values as pass through, you could do that in line (no table variable required). — Xedni
– Xedni, Commented Sep 25, 2018 at 23:10

Colin G · Accepted Answer · 2018-09-25 22:30:12Z

1

A table variable insert is really not different than a regular insert. Don't use temp tables. You can alter the table as well, or just declare it initially with that fourth column and allow it to be NULL.

INSERT INTO @CustomerTable (UniquePatientUID, ResultValue, PracticeDescription)
VALUES(@CustomerID, @CustomerValue, @CustomerDescription);

Don't forget to return the FLOAT.

answered Sep 25, 2018 at 22:30

Colin G

1656 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tsouchlarakis Over a year ago

This is very helpful, exactly what I needed! Thank you.

D-Shih · Accepted Answer · 2018-09-25 22:30:55Z

1

Table Variable is a table so, you can just use INSERT INTO ... VALUES....

 INSERT INTO @CustomerTable (UniquePatientUID,ResultValue,PracticeDescription ) 
 VALUES
 (@UniquePatientUID, @ResultValue , @PracticeDescription)

answered Sep 25, 2018 at 22:30

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

1 Comment

tsouchlarakis Over a year ago

This along with Colin G's answer helped me out, thank you!

Xedni · Accepted Answer · 2018-09-25 23:23:05Z

1

Unless you need a table variable for some specific reason, why not just work with the variables as a derived table expression? i.e.

;with inputs (UniquePatientUID, ResultValue, PracticeDescription) as
(
    select @UniquePatientUID, @ResultValue, @PracticeDescription
)
select *
from inputs

Table variables fall out of scope after the function call, and you can't pass table types in or out of functions either. So really all a table variable does here is serve as a means of place keeping that's more familiar to SQL developers. But they're not free, which is the only reason I'm curious what your use case is.

If you don't need to return them as a set or something similar, you can just interact with the variables directly too.

edited Sep 25, 2018 at 23:23

answered Sep 25, 2018 at 23:17

Xedni

4,7913 gold badges22 silver badges38 bronze badges

2 Comments

tsouchlarakis Over a year ago

This is a nice answer, and to follow up on your question, after creating this table I need to perform much manipulation on that table in the form primarily of UPDATE and ALTER TABLE statements. For that reason I thought it would be easier to work with a table variable, since it allows at least UPDATE operations, like temp tables do. Can you work with derived table expressions in this way as well?

Xedni Over a year ago

You've actually struck upon the deeper point around why you're having to entertain a table variable in the first place. Functions in SQL-land can't have "side effects". Among many other things, that means no updates or table alterations can have any permanent impact outside the scope of the function. Table variables are allowed since they die after the function call, but temp tables can persist (and thus potentially "do stuff"). SQL functions are dissimilar from what one might loosely call a function in something like Javascript or C#. What you're describing can only be done in a proc.

Collectives™ on Stack Overflow

Table Variable in SQL Server Function from Input Columns

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related