Dynamic NVARCHAR(MAX) Being Truncated When 'inlining' a NVARCHAR(MAX) Parameter

4 votes

2 answers

265 views

                          The @SQL variable in the statement below is always truncated when I "inline" a variable. If I send the variable in and print it does not truncate to 4000, however, I can't bind the sent in variable into a USE [@DatabaseName] statement. How to get around.

**This works, however, the database name is hardcoded in the USE statement**

    DECLARE @DatabaseName NVARCHAR(MAX) = 'MyDatabase'
    DECLARE @Sql NVARCHAR(MAX)=
    '
    USE [MyDatabase]
    PRINT @DatabaseName
    SELECT
    ...<20,000 more chars
    '
    
    DECLARE @params NVARCHAR(MAX) ='@DatabaseName NVARCHAR(MAX)'
    EXEC sp_executesql @SQL, @params,@DatabaseName

**This throws an exceptionn with the message '*Database '@DatabaseName' does not exist. Make sure that the name is entered correctly*.'**

    DECLARE @DatabaseName NVARCHAR(MAX) = 'MyDatabase'
    DECLARE @Sql NVARCHAR(MAX)=
    '
    USE [@DatabaseName]
    PRINT @DatabaseName
    SELECT
    ...<20,000 more chars
    '
    
    DECLARE @params NVARCHAR(MAX) ='@DatabaseName NVARCHAR(MAX)'
    EXEC sp_executesql @SQL, @params,@DatabaseName

**This truncates the query to NVARCHAR(4000)**

    DECLARE @DatabaseName NVARCHAR(MAX) = 'MyDatabase'
    DECLARE @Sql NVARCHAR(MAX)=
    '
    USE ['+@DatabaseName+']
    PRINT @DatabaseName
    SELECT
    ...<20,000 more chars
    '
    
    DECLARE @params NVARCHAR(MAX) ='@DatabaseName NVARCHAR(MAX)'
    EXEC sp_executesql @SQL, @params,@DatabaseName

**Also tried using N'string' without success**

    DECLARE @DatabaseName NVARCHAR(MAX) = N'MyDatabase'
    DECLARE @Sql NVARCHAR(MAX)=
    N'
    USE ['+@DatabaseName+']
    PRINT @DatabaseName
    SELECT
    ...<20,000 more chars
    '
    
    DECLARE @params NVARCHAR(MAX) =N'@DatabaseName NVARCHAR(MAX)'
    EXEC sp_executesql @SQL, @params,@DatabaseName

I think I understand why the USE [@Variable] does not work, whereas I don't know how to explain why USE ['+@Variable+'] is being truncated down to 4000 when bothe vars are max unless sql server is casting down in sp_executesql.

Anyway, does anyone know of a workaround to make this work. I basically want to dynamically target any database with a sql statement larger than 4000 nchars. 

                        

Asked by Ross Bush (683 rep)

Mar 27, 2025, 12:56 PM
Last activity: Mar 27, 2025, 02:16 PM

Dynamic NVARCHAR(MAX) Being Truncated When 'inlining' a NVARCHAR(MAX) Parameter

Related Questions