Pass a table as parameter in openquery

Question

I've created a datatype and declared a table of this type which I intend to pass as a parameter to an OPENQUERY statement. OPENQUERY returns an error that the object has no columns.
Firstly, the table parameter is defined as follows:

CREATE TYPE LooseTimecardsTableType AS TABLE ([LABORKEY] [float] NULL)          
GO

DECLARE @DataTable AS LooseTimecardsTableType
INSERT INTO @DataTable
SELECT DISTINCT WOBase.LABORKEY
FROM Lab_WO_DataWH AS WOBase 
left outer JOIN Lab_hrs_DataWH LabHrsWH ON LabHrsWH.WORKORDERLABORKEY = WOBase.LABORKEY
WHERE LabHrsWH.WORKORDERLABORKEY IS NULL AND WOBase.LABORKEY IS NOT NULL AND WOBase.LABORPRICE <> 0 

The data table returns a single column of LABORKEY's which I want use to restrict the number of records from the OPENQUERY. This in turn is defined as follows:

DECLARE @SQLString NVARCHAR(500), @TableVariable LooseTimecardsTableType
SET @SQLString =  N'SELECT * FROM OPENQUERY(Remoteserver, ''SELECT DISTINCT
    DA.USERNAME, DA.WORKORDERLABORKEY, LB.PERFORMEDBY 
    FROM
    REMOTE.WORKORDERDETAILAUDITS DA
    JOIN REMOTE.WORKORDERLABORBASE LB ON LB.LABORKEY = DA.WORKORDERLABORKEY
    JOIN @TableVariable ON @TableVariable.LABORKEY = LB.LABORKEY
    WHERE DA.WORKORDERAUDITCATEGORY = 0'')'

EXECUTE sp_executesql @SQLString, N'@TableVariable LooseTimecardsTableType READONLY', @DataTable

Please assist in getting this to work

Answer 1

The table variable cannot be passed as a parameter to OPENQUERY. As my table had only a single column I was able to convert it to a string and pass the string to OPENQUERY as a parameter. I did this within a procedure that accepts a table variable. After parsing the table to a string, the procedure also runs the OPENQUERY.

CREATE PROCEDURE  PRM_LIST ( @TableVariable LooseTimecardsTableType  READONLY)
AS
DECLARE
@LBKY_NVAR NVARCHAR(2500),
@POINTER INT,
@SQLString NVARCHAR(max)

SELECT @POINTER =  MIN(LABORKEY) FROM @TableVariable
WHILE @POINTER IS NOT NULL
BEGIN
SET @LBKY_NVAR = IIF(@LBKY_NVAR IS NULL,'('+''''+ CONVERT(VARCHAR,@POINTER) + '''',
@LBKY_NVAR + ',' + ''''+ CONVERT(VARCHAR,@POINTER) + '''')
SELECT @POINTER =  MIN(LABORKEY) FROM @TableVariable WHERE LABORKEY > @POINTER
END
SET @LBKY_NVAR = @LBKY_NVAR +  ')'
SET @LBKY_NVAR = REPLACE(@LBKY_NVAR,'''','''''')

SET @SQLString =
N'SELECT * FROM OPENQUERY(REMOTE, ''SELECT DISTINCT
DA.USERNAME, DA.WORKORDERLABORKEY, LB.PERFORMEDBY 
FROM
REMOTE.WORKORDERDETAILAUDITS DA
JOIN REMOTE.WORKORDERLABORBASE LB ON LB.LABORKEY = DA.WORKORDERLABORKEY
WHERE DA.WORKORDERAUDITCATEGORY = 0
AND LB.LABORKEY IN '+CAST(@LBKY_NVAR AS nvarchar(2500))+ N'
'')'
EXEC (@SQLString)
GO

The procedure call is:

EXECUTE PRM_LIST @DATATABLE