TVP Parameter Schema Name Not Included in sp_executesql @params

[crs2007]

TVP Parameter Schema Name Not Included in sp_executesql @params

Bug Description

When using Table-Valued Parameters (TVP) with User-Defined Table Types that are not in the dbo schema, the schema name is missing from the @params parameter in the generated sp_executesql call. This causes a "Cannot find data type" error.

Environment

• node-mssql version: v11.0.1
• Node.js version: 22.17.0
• SQL Server version: 2022
• Driver: tedious (default)

Expected Behavior

When using a TVP with a schema-qualified UDT like AI.UDT_StringArray, the generated sp_executesql should include the schema name in the @params parameter:

exec sp_executesql 
  @statement=N'EXECUTE AI.USP_GetCodeObjects @I_ObjectList',
  @params=N'@I_ObjectList AI.UDT_StringArray readonly',  -- Should include schema name
  @I_ObjectList=@p3

Actual Behavior

The generated sp_executesql omits the schema name from the @params parameter:

exec sp_executesql 
  @statement=N'EXECUTE AI.USP_GetCodeObjects @I_ObjectList',
  @params=N'@I_ObjectList UDT_StringArray readonly',  -- Missing schema name
  @I_ObjectList=@p3

This results in the error: Cannot find data type UDT_StringArray

Reproduction Steps

1. Create the Database Schema and UDT

-- Create a schema other than dbo
CREATE SCHEMA AI;

-- Create a UDT in the AI schema
CREATE TYPE AI.UDT_StringArray AS TABLE(
    [Name] [sysname] NOT NULL
);

-- Create a stored procedure that uses the UDT
CREATE PROCEDURE AI.USP_TestProcedure
    @InputList AI.UDT_StringArray READONLY
AS
BEGIN
    SELECT Name FROM @InputList;
END

2. Node.js Code to Reproduce the Issue

import * as sql from 'mssql';

async function testTVPBug() {
    const config = {
        user: 'your_username',
        password: 'your_password',
        server: 'your_server',
        database: 'your_database',
        options: {
            encrypt: true,
            trustServerCertificate: true
        }
    };

    try {
        const pool = await sql.connect(config);
        const request = pool.request();

        // Create TVP with schema-qualified name
        const tvp = new sql.Table('AI.UDT_StringArray');
        tvp.columns.add('Name', sql.NVarChar(128), { nullable: false });
        tvp.rows.add('TestValue1');
        tvp.rows.add('TestValue2');

        // Add TVP parameter - this is where the bug occurs
        request.input('InputList', tvp);

        // Execute the stored procedure
        const result = await request.execute('AI.USP_TestProcedure');
        console.log('Success:', result);

    } catch (error) {
        console.error('Error:', error.message);
        // Error: Cannot find data type UDT_StringArray
    }
}

testTVPBug();

3. Alternative approaches that also fail

// Approach 1: Using sql.TVP
request.input('InputList', sql.TVP('AI.UDT_StringArray'), tvp);

// Approach 2: Using explicit type
const tvpType = new sql.TVP('AI.UDT_StringArray');
request.input('InputList', tvpType, tvp);

Root Cause Analysis

The issue appears to be in how node-mssql generates the parameter metadata for sp_executesql. When a TVP is used with a schema-qualified UDT name, the library correctly creates the TVP with the full schema name but fails to include the schema name in the @params string of the sp_executesql call.

Workaround

Currently, the only workaround is to use raw SQL without parameters:

const valuesList = names.map(name => `(N'${name.replace(/'/g, "''")}')`).join(',');
const query = `
    DECLARE @InputList AI.UDT_StringArray;
    INSERT INTO @InputList (Name) VALUES ${valuesList};
    EXECUTE AI.USP_TestProcedure @InputList;
`;
const result = await request.query(query);

Proposed Solution

The library should preserve the schema name when generating the parameter metadata for TVP parameters. When a TVP is created with a schema-qualified name like AI.UDT_StringArray, the same schema-qualified name should be used in the @params parameter of the sp_executesql call.

Additional Context

This issue affects any User-Defined Table Type that is not in the default dbo schema. It's common in enterprise environments to organize database objects into different schemas for security and organizational purposes.

The issue can be reproduced with any non-dbo schema name (e.g., MySchema.MyTableType, HR.EmployeeTableType, etc.).

[dhensby]

Thanks for taking the time to investigate this problem and write out such a detailed issue. It definitely looks like a problem!

Do you have any interest in authoring a fix for this? It's not something I have to deal with day-to-day and having someone who does author and verify the fix would be much more reliable IMO.

[crs2007]

Thanks so much, Daniel, for the opportunity.
I've gone ahead and implemented a fix for the issue: #1763