SQL Server - Pass Table as Parameter to function within select

Question

Have setup a TableType as

create type KeyPairTable as Table (KeyValue nvarchar(50), 
                                   [Description] nvarchar(500))

I have a table which can be simplified to

create table Elements (SharedKey int,
                       ElementName nvarchar(50), 
                       SourceSQL nvarchar(500))

and I have a function with header

create function RunElement(@SharedKey int,
                           @ValuesTable KeyPairTable readonly)

now what I need to do is effectively:

select RunElement(SharedKey, 
                  case when SourceSql is not null then [X] 
                       else [Y] 
                  end) from Elements

where [X] is taking the SQL statement stored in SourceSQL and passing it in as a KeyPairTable and [Y] is an empty table (e.g. it won't be used / isn't needed)

I would like to avoid cursors / loops and do this in a select as this is a simplification of the problem and the select actually joins several tables.

Answer 1

what im asking is how to get a 'table' to pass into a function within a select

Ok, there are some parts of your example code that still does not make sense to me, but I believe this example should be what you are looking for (or close enough you will understand.)

DECLARE @myTable KeyPairTable 

INSERT INTO @myTable (KeyValue, [Description])
  SELECT SharedKey, 
         CASE WHEN SourceSql IS NULL THEN [X] ELSE [Y] END
  FROM Elements

SELECT RunElement(SharedKey, @myTable)