CODEWITHSUNDEEP
sql-servert-sqlsqldatatypesnvarchar
e4rthdog
e4rthdog

Reputation: 5223

Stored procedure variable defined nvarchar(max) gets truncated to 4000 chars

When i print the @v_sql parameter i get 4000 chars.

How i fix this?

Source:

BEGIN
    SET NOCOUNT ON;
    DECLARE @v_sql nvarchar(max)
    SELECT @v_sql = N'';
    IF UPPER(@action)='DISABLE'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+ QUOTENAME(name,'[')+' ON ' + quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' DISABLE;'+char(10)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;   
    END
    IF UPPER(@action)='REBUILD'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+QUOTENAME(name,'[')+' ON ' +  quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' REBUILD WITH(SORT_IN_TEMPDB=ON);'+char(10)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;
    END
    IF @v_sql <> ''
    BEGIN
        print @v_sql
        --EXEC sp_executesql @v_sql;
    END
END

Upvotes: 1

Views: 1554

Answers (3)

Ajay2707
Ajay2707

Reputation: 5798

You are right, when we get the data in PRINT it is truncated, but it will not truncated when actual data executed, the only thing is we have to define length as MAX.

Sqlserver support 8000 charcter also. Only thing , you can't see, but it will executed by sqlserver.

Means EXEC sp_executesql @v_sql; will execute sqlserver append as many long string. and executed.

I think you get an error, at that we need to print actual query. Right, if yes, then I have one solution like, you can break your code which is independently execute or work.

You can use hardcore value from one the resultset, check that will create an issue, then remove/add some condition which filter the more data.

Finally you got the data which create an issue.

Run only this portion and then second portion.

DECLARE @v_sql nvarchar(max)
    SELECT @v_sql = N'';
    IF UPPER(@action)='DISABLE'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+ QUOTENAME(name,'[')+' ON ' + quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' DISABLE;'+char(10) + char(13)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;   
    END
    IF @v_sql <> ''
       BEGIN
         select @v_sql
         --EXEC sp_executesql @v_sql;
       END

nvarchar(max) still being truncated

Upvotes: 1

e4rthdog
e4rthdog

Reputation: 5223

In my case i wanted to be able to copy paste the results of the sp so the solution is:

1) Replace print with select 2) Add char(13) after char(10)

BEGIN
    SET NOCOUNT ON;
    DECLARE @v_sql nvarchar(max)
    SELECT @v_sql = N'';
    IF UPPER(@action)='DISABLE'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+ QUOTENAME(name,'[')+' ON ' + quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' DISABLE;'+char(10) + char(13)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;   
    END
    IF UPPER(@action)='REBUILD'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+QUOTENAME(name,'[')+' ON ' +  quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' REBUILD WITH(SORT_IN_TEMPDB=ON);'+char(10) + char(13)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;
    END
    IF @v_sql <> ''
    BEGIN
        select @v_sql
        --EXEC sp_executesql @v_sql;
    END
END

Upvotes: 0

fly_ua
fly_ua

Reputation: 1054

it is truncated by print - "The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000)."

Upvotes: 1

Related Questions