MS SQL problems with dynamic sql and sp_executesql with type

Question

I am a newbie within tablevalued types in MS SQL. But until now I have searched a lot to find a solution. Maybe I don't know how to use them :-( I have a problem using them and the error says (translated into english)

SqlVariantTable is not compatible with NVARCHAR.

So I really hope that someone can give me a hint that points into the right direction.

My stored procedure looks like this:

CREATE PROCEDURE [dbo].[DuplicateLevel4] @UserID INT AS
SET NOCOUNT ON
DECLARE @dubletNo NVARCHAR(4000),
        @subDubletNo NVARCHAR(4000),
        @Vennenr NVARCHAR(4000),
        @fornavn NVARCHAR(4000),
        @shortenfornavn NVARCHAR(4000),
        @efternavn NVARCHAR(4000),
        @adresse NVARCHAR(4000),
        @postnr NVARCHAR(4000),
        @telefon NVARCHAR(4000),
        @tlf1 NVARCHAR(4000),
        @email NVARCHAR(4000),
        @done BIT,
        @oldTime DATETIME,
        @selectsql NVARCHAR(4000),
        @CountFriendID NVARCHAR(4000),
        @ParamString NVARCHAR(4000)

SET @dubletNo = 1

DECLARE @Table dbo.SqlVariantTable;
            DELETE FROM @Table
            INSERT INTO @Table VALUES (@CountFriendID)
            INSERT INTO @Table VALUES (@vennenr);
            SET @ParamString = '@CountFriendID NVARCHAR(4000) output, @vennenr NVARCHAR(4000)';
            SET @selectsql = '';
            SET @selectsql = @selectsql + 'SELECT @CountFriendID = Count(Vennenr) ';
            SET @selectsql = @selectsql + 'FROM Medlemsdata ';
            SET @selectsql = @selectsql + 'LEFT OUTER JOIN Postnumre ';
            SET @selectsql = @selectsql + 'RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID ';
            SET @selectsql = @selectsql + 'WHERE (Vennenr <> @Vennenr) ';
            IF @shortenfornavn IS NOT NULL BEGIN SET @selectsql = @selectsql + 'AND Upper_Fornavn_FirstPart = @shortenfornavn ' SET @ParamString = @ParamString + ', @shortenfornavn varchar(max)' INSERT INTO @Table VALUES (@shortenfornavn) END ELSE SET @selectsql = @selectsql + 'AND Upper_Fornavn_FirstPart IS NULL ';
            IF @efternavn      IS NOT NULL BEGIN SET @selectsql = @selectsql + 'AND Upper_Efternavn = @efternavn '              SET @ParamString = @ParamString + ', @efternavn varchar(max)'      INSERT INTO @Table VALUES (@efternavn) END ELSE SET @selectsql = @selectsql + 'AND Upper_Efternavn IS NULL ';
            IF @email          IS NOT NULL BEGIN SET @selectsql = @selectsql + 'AND Upper_Email = @email '                      SET @ParamString = @ParamString + ', @email varchar(max)'          INSERT INTO @Table VALUES (@email) END ELSE SET @selectsql = @selectsql + 'AND Upper_Email IS NULL ';
            
            SET @selectsql = @selectsql + 'AND (ISNULL(telefon,'''') LIKE ''%'' + @telefon1 + ''%'') AND (LEN(@telefon2) > 7) ';
            SET @selectsql = @selectsql + 'AND vennenr > 0 ';
            SET @selectsql = @selectsql + 'AND vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) ';
            SET @selectsql = @selectsql + 'AND vennenr NOT IN (SELECT FriendID FROM DuplicateList) ';

            SET @ParamString = @ParamString + ', @telefon1 varchar(max), @telefon2 varchar(max)';

            EXECUTE sys.sp_executesql @selectsql, @ParamString, @Table; 

            IF @CountFriendID > 0 BEGIN
                DELETE FROM @Table
                INSERT INTO @Table VALUES (@dubletNo)
                INSERT INTO @Table VALUES (@subDubletNo)
                INSERT INTO @Table VALUES (@vennenr);
                SET @ParamString = '@dubletNo NVARCHAR(4000), @subDubletNo NVARCHAR(4000), @vennenr NVARCHAR(4000)'
                SET @selectsql = '';
                SET @selectsql = @selectsql + 'INSERT INTO DuplicateList (FriendID, DubletNo, SubDubletNo, [UseOrganisation], [UseFornavn], [UseEfternavn], [UseAdresse], [UsePostCode], [UseTlf1], [UseTlf2], [UseEmail], [Choice], [AKeep], [UseBornDate]) ';
                SET @selectsql = @selectsql + 'SELECT Vennenr, @dubletNo, ROW_NUMBER() OVER (ORDER BY Vennenr) + @subDubletNo, 0,0,0,0,0,0,0,0,0,0,0 ';
                SET @selectsql = @selectsql + 'FROM Medlemsdata ';
                SET @selectsql = @selectsql + 'LEFT OUTER JOIN Postnumre ';
                SET @selectsql = @selectsql + 'RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID ';
                SET @selectsql = @selectsql + 'WHERE (Vennenr <> @Vennenr) ';
                IF @shortenfornavn IS NOT NULL BEGIN SET @selectsql = @selectsql + 'AND Upper_Fornavn_FirstPart = @shortenfornavn ' SET @ParamString = @ParamString + ', @shortenfornavn varchar(max)' INSERT INTO @Table VALUES (@shortenfornavn) END ELSE SET @selectsql = @selectsql + 'AND Upper_Fornavn_FirstPart IS NULL ';
                IF @efternavn      IS NOT NULL BEGIN SET @selectsql = @selectsql + 'AND Upper_Efternavn = @efternavn '              SET @ParamString = @ParamString + ', @efternavn varchar(max)'      INSERT INTO @Table VALUES (@efternavn) END ELSE SET @selectsql = @selectsql + 'AND Upper_Efternavn IS NULL ';
                IF @email          IS NOT NULL BEGIN SET @selectsql = @selectsql + 'AND Upper_Email = @email '                      SET @ParamString = @ParamString + ', @email varchar(max)'          INSERT INTO @Table VALUES (@email) END ELSE SET @selectsql = @selectsql + 'AND Upper_Email IS NULL ';
            
                SET @selectsql = @selectsql + 'AND (ISNULL(telefon,'''') LIKE ''%'' + @telefon1 + ''%'') AND (LEN(@telefon2) > 7) ';
                SET @selectsql = @selectsql + 'AND vennenr > 0 ';
                SET @selectsql = @selectsql + 'AND vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) ';
                SET @selectsql = @selectsql + 'AND vennenr NOT IN (SELECT FriendID FROM DuplicateList) ';

                SET @ParamString = @ParamString + ', @telefon1 varchar(max), @telefon2 varchar(max)';
                INSERT INTO @Table VALUES (@telefon) 
                INSERT INTO @Table VALUES (@telefon) 

                EXECUTE sys.sp_executesql @selectsql, @ParamString, @Table;
            END

My type look like this:

CREATE TYPE [dbo].[SqlVariantTable] AS TABLE(
    [Value] [NVARCHAR(4000)] NULL
)

I really hope that someone can give me an idea to what to look after, so I can avoid this error.
Thanks in advance,
Michael

I don't doubt for a second that we need all of that code. Take the time to give us a [mre]. *(Side note, why are all your parameters an `nvarchar(4000)`? I wouldn't expect someone's name, for example, to be 4,000 characters long, nor an email address, and with names like `@dubletNo` and `@subDubletNo` that suggests that they hold numerical data, not string data.* — Thom A, Nov 17 '20 at 12:33
The error, however, is telling you the problem. I assume you are comparing a `varchar` to a `sql_variant` or trying to concatenate one to it (example `SELECT CONVERT(sql_variant, 'abc') + 'def';` returns the error *"The data types sql_variant and varchar are incompatible in the add operator."*). — Thom A, Nov 17 '20 at 12:35
@Larnu Sorry for the long procedure - I have now shortened it. — Michael Eriksen, Nov 17 '20 at 12:38
Honestly, looking at the above, it looks like you've massively overly complicated the problem as well. It should be a different question, but I would suggest that you may want to ask about the Procedure you have as well, and how it can be simplified; ensuring you explain the goals in full with sample data. The fact that you use a `sql_variant` here feels like the result of an [XY Problem](http://xyproblem.info). `sql_variant` isn't an easy data type to use; in fact most experts avoid it use apart from in very unique scenarios. I think I've only had one "valid" use for it, and I didn't like it. — Thom A, Nov 17 '20 at 12:41
Why are you using `sql_variant` in your type at all? As far as I could see, you only insert varchar into it... — Tyron78, Nov 17 '20 at 12:44
@Larnu I know that it is a big procedure - but what would you recommend me to do next? — Michael Eriksen, Nov 17 '20 at 12:44
@Tyron78 I have now changed it to a nvarchar(4000) - but with no success :-( Do you have any suggestions ? — Michael Eriksen, Nov 17 '20 at 12:47
I can't run the above so it's impossible for me to know where, specifically, the error happens, @MichaelEriksen . That's why I ask for a [mre], with emphasis on the **minimal**. But my point from my [second comment](https://stackoverflow.com/questions/64875292/ms-sql-problems-with-dynamic-sql-and-sp-executesql-with-type?noredirect=1#comment114699617_64875292) explains *why* you're getting the error. Without a [mre] it's up to you to find where that's happening. — Thom A, Nov 17 '20 at 12:47
But, like I said, I can't (and nor can one else here) run that Procecure. *I* don't have access to your instance, and that means I can't even see what the dynamic statement you're producing even looks like. I can't repeat again, take the time to produce a [mre]. We can't help you here without one, other than to tell you what I did in my [second comment](https://stackoverflow.com/questions/64875292/ms-sql-problems-with-dynamic-sql-and-sp-executesql-with-type?noredirect=1#comment114699617_64875292) and that *you* need to find out where that comparison/concatenation is happening. — Thom A, Nov 17 '20 at 13:00
@MichaelEriksen try to create a running example on SQLFiddle - including Testdata etc. This way it should be easier to understand. — Tyron78, Nov 17 '20 at 13:40
@MichaelEriksen no, but you can add your create table statements to the Schema-Window (you will see when you open SQLFiddle - be sure to switch to SQLServer there, since MySQL seems to be default) and inserts etc. to the other window. — Tyron78, Nov 17 '20 at 15:30

MS SQL problems with dynamic sql and sp_executesql with type

0 Answers0