Passing Table Parameter to a Table Function in SQL Server

suresh sarda

10y943

I have create the table-function with table valued parmaeter. I am able to use it as below code

DECLARE @dimtypetable ddm.[VAR_DIMENSIONDETAILS] ;
INSERT INTO @dimtypetable
SELECT * FROM (VALUES('FAMILY',NULL,NULL,NULL,NULL,NULL),
('SUB_FAMILY',NULL,NULL,NULL,NULL,NULL)
)S(A,B,C,D,E,F)
--SELECT * FROM @dimtypetable
SELECT * FROM DDM.FN_GETDIMENSION_TBL(@dimtypetable);

Now I wan to use this table valued function as select statement using the Table-constructor thru cross apply or simple select statement.

SELECT * FROM DDM.FN_GETDIMENSION_TBL((SELECT * FROM (VALUES('FAMILY',NULL,NULL,NULL,NULL,NULL))S(A,B,C,D,E,F)));

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 206, Level 16, State 2, Line 1
Operand type clash: varchar is incompatible with VAR_DIMENSIONDETAILS

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
Unfortunately you can't send any temp variable to procedure or function. In order to avoid the problem create a user defined table type like below:
CREATE TYPE TableType
AS TABLE (NameVARCHAR(50))
GO

DECLARE @myTable TableType

INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT * FROM @myTable

CREATE FUNCTION FN_GETDIMENSION_TBL(@TableName TableType READONLY)
RETURNS VARCHAR(50)
AS
BEGIN
     DECLARE @name VARCHAR(50)
     SELECT TOP 1 @name = Name FROM @TableName
RETURN @name
END
Vishal Jadav10y ago
HI,
For the first error try to use CTE.
Hope it helps.