Passing Table Parameter to a Table Function in SQL Server

Question

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

Manas Mohapatra · Answer

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 Jadav · Answer

HI For the first error try to use CTE. Hope it helps.