Operand type clash: varchar is incompatible with User-Defined Table Type

Question

I created a User-Defined Table Type:

CREATE TYPE dbo.ListTableType AS TABLE(
    ITEM varchar(500) NULL
)

I leverage it in a function:

CREATE FUNCTION dbo.fn_list_to_string
(
    @LIST dbo.ListTableType READONLY
)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @RESULT varchar(max)
    SET @RESULT = ''

    DECLARE @NL AS CHAR(2) = CHAR(13) + CHAR(10)

    SELECT @RESULT = @RESULT + ITEM + @NL FROM @LIST
    SET @RESULT = SUBSTRING(@RESULT, 1, LEN(@RESULT) - 1)

    RETURN @RESULT
END

Finally, I try to use this function in a simple select:

SELECT
    P.PROGRAM_ID,
    PROGRAM_NAME,
    PROGRAM_DESC,
    P.STATUS_ID,
    STATUS_DESC,
    P.CONTACT_SID,
    I.FIRST_NAME + ' ' + I.LAST_NAME as CONTACT_NAME,
    P.CLARITY_ID,
    dbo.fn_list_to_string(
        (   SELECT CONVERT(varchar,CLARITY_ID) as ITEM
            FROM dbo.MUSEUM_PROGRAM_PROJECTS as A
            JOIN dbo.MUSEUM_PROJECTS as B on B.PROJECT_ID = A.PROJECT_ID
            WHERE PROGRAM_ID = P.PROGRAM_ID )
    ) as PROJECT_CLARITY_IDS
FROM dbo.MUSEUM_PROGRAMS as P
LEFT JOIN dbo.MUSEUM_PROGRAM_STATUS_TYPES as S on S.STATUS_ID = P.STATUS_ID
LEFT JOIN dbo.v_IDVAULT_ENRICHED_CURRENT_EMPLOYEES as I on I.[SID] = P.CONTACT_SID

But I get this error:

Operand type clash: varchar is incompatible with ListTableType

Any idea why? Also if there's another [more elegant] way to achieve what I'm trying to do I'm open to suggestions as well! Thanks in advance!

(1) [don't use `varchar` without length](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx) (2) you can't substitute a subquery for a table type in the input for a function, making it seem like you actually had a table type. I suggest you post what result you're actually trying to get and I think there is a much simpler way with `FOR XML PATH`. — Aaron Bertrand, Aug 07 '13 at 14:15
For example, the subquery can return two CLARITY_ID's: 25245 and 14355. I want to display this as a string with new lines. — elisa, Aug 07 '13 at 14:19
Search for `GROUP_CONCAT` hacks in SQL Server to project a list into a scalar varchar, e.g. http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server. But your idea is interesting :) — StuartLC, Aug 07 '13 at 14:22
One more note: Even if you do want to use a function you don't need table type. You can just send a PROGRAM_ID to function and make it read the values itself (instead of subquery) before doing concatenation (either with FOR XML or your original approach). — Nenad Zivkovic, Aug 07 '13 at 14:48

score 2 · Accepted Answer · answered Aug 07 '13 at 14:26

Here is a simple demonstration of the FOR XML PATH technique which does all of this with a very simple subquery and no table types or extremely inefficient multi-statement table-valued functions etc.

USE tempdb;
GO

CREATE TABLE dbo.P(Program_ID INT);

CREATE TABLE dbo.M(Clarity_ID INT, Program_ID INT);

INSERT dbo.P VALUES(1),(2),(3),(4);

INSERT dbo.M VALUES(1,1),(1,2),(2,3),(3,2),(1,4),(4,1);

SELECT
    P.PROGRAM_ID,
    PROJECT_CLARITY_IDS = STUFF((
      SELECT CHAR(13)+CHAR(10)+CONVERT(VARCHAR(12),Clarity_ID)
        FROM dbo.M WHERE Program_ID = p.Program_ID
        FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)'),1,2,'')
 FROM dbo.P AS p;

SQLfiddle demo

The output doesn't look right in SQLfiddle or in results to grid in Management Studio, because they strip out carriage returns/line feeds for display purposes, but you can replace CHAR(13)+CHAR(10) with two commas or semi-colons or something to verify that there are two characters there.

Nenad Zivkovic · Answer 2 · 2013-08-07T14:38:45.143

0

Using STUFF..FOR XML PATH construct for concatanation in combination with CTE will get the results you'd like. Something like this:

WITH CTE_PROJECT_CLARITIES AS
(
    SELECT DISTINCT PROGRAM_ID
            , STUFF((
                SELECT CHAR(13) + CHAR(10) + CONVERT(varchar(11),CLARITY_ID) 
                FROM dbo.MUSEUM_PROGRAM_PROJECTS as A
                    JOIN dbo.MUSEUM_PROJECTS as B on B.PROJECT_ID = A.PROJECT_ID
                WHERE A.PROGRAM_ID = X.PROGRAM_ID
                FOR XML PATH ('')),1,2,'') AS PROJECT_CLARITY_IDS
    FROM MUSEUM_PROGRAM_PROJECTS X
)
SELECT
    P.PROGRAM_ID,
    PROGRAM_NAME,
    PROGRAM_DESC,
    P.STATUS_ID,
    STATUS_DESC,
    P.CONTACT_SID,
    I.FIRST_NAME + ' ' + I.LAST_NAME as CONTACT_NAME,
    P.CLARITY_ID,
    X.PROJECT_CLARITY_IDS
FROM dbo.MUSEUM_PROGRAMS as P
LEFT JOIN dbo.MUSEUM_PROGRAM_STATUS_TYPES as S on S.STATUS_ID = P.STATUS_ID
LEFT JOIN dbo.v_IDVAULT_ENRICHED_CURRENT_EMPLOYEES as I on I.[SID] = P.CONTACT_SID
LEFT JOIN CTE_PROJECT_CLARITIES X ON X.PROGRAM_ID = p.PROGRAM_ID

SQLFiddle DEMO (not sure if I got the columns right, but you'll get the idea)

edited Aug 07 '13 at 14:38

answered Aug 07 '13 at 14:33

Nenad Zivkovic

18,221
6
42
55

I did go the FOR XML PATH route as Aaron Bertrand suggested but would not have been able to use a CTE because the select statement is in a view. – elisa Aug 07 '13 at 14:35
@elsia I can't see how a select statement being in a view can prevent the use of CTE. – Nenad Zivkovic Aug 07 '13 at 14:37
@AaronBertrand Changed. It does look a bit ugly in FIddle :) – Nenad Zivkovic Aug 07 '13 at 14:40
@Nenad yes I noted that in my answer. – Aaron Bertrand Aug 07 '13 at 14:41

Operand type clash: varchar is incompatible with User-Defined Table Type

2 Answers2