CODEWITHSUNDEEP
sqlsql-servert-sql
tsouchlarakis
tsouchlarakis

Reputation: 1619

Table Variable in SQL Server Function from Input Columns

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?

Upvotes: 1

Views: 474

Answers (3)

Xedni
Xedni

Reputation: 4695

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.

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

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

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

Upvotes: 1

Colin G
Colin G

Reputation: 165

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.

Upvotes: 1

Related Questions