-1

I'm trying to create a dynamic query that retrieves the columns of a table (as new columns might be added in the future) and this query might also be used on multiple databases with different table configurations.

The purpose right now is to compare if there are any changes in the values of the columns between OITM and AITM tables:

  • OITM contains the current record
  • AITM is a history table that contains the history records

AITM and OITM column structures are always the same except for the LogInstanc column which only exists in the AITM table:

DECLARE @ListOfColsValTabDel NVARCHAR(MAX) = '020650005'
DECLARE @SQL1 NVARCHAR(MAX)
SET @SQL1 =  'select COUNT(*) from OITM o 
                        LEFT JOIN AITM a on o."ItemCode" = a."ItemCode" 
                                and a."LogInstanc" = (Select 
                                                         Max(x."Loginstanc") 
                                                            from AITM x 
                                                            where x."ItemCode" =''' + @ListOfColsValTabDel
                                                        +''' 
                                                        )
                        WHERE o."ItemCode" = '''+  @ListOfColsValTabDel
                          + ''' AND (' 

                          -- Below Sub query returns string of 9975 
                          +
                                (select LEFT (REPLACE(stuff((SELECT 'o.'+Name + ' <>  a.'+ Name + ' or ' FROM sys.columns WHERE object_id = OBJECT_ID('dbo.OITM') FOR XML PATH('')), 1, 0, '') , '&lt;&gt;','<>')
                                            ,LEN(REPLACE(stuff((SELECT 'o.'+Name + ' <>  a.'+ Name + ' or ' FROM sys.columns WHERE object_id = OBJECT_ID('dbo.OITM') FOR XML PATH('')), 1, 0, '') , '&lt;&gt;','<>')) - 3)) 
                                +')'

PRINT (@SQL1) -- Gets truncated at 4000 characters
--exec(@SQL1)

This prints 4000 characters while the total string should be around 11000 as the sub query for retrieving the columns is already 9975 characters.

I know you can execute dynamic SQL with (@SQL1 4000 characters + @SQL2 4000 characters + SQL 3 4000 characters).

But how to split this string up in variables of 4000 characters?

An alternative method people mention is to cast the string as NVARCHAR(MAX) but this didn't seem to help either.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Chester van Ree
  • 105
  • 8
  • You tagged two database systems, Microsoft SQL Server and SAP HANA. Please keep only the one you use, as the answer may vary depending on the database system. – Sandra Rossi Jan 19 '23 at 14:22

1 Answers1

3

PRINT will not output more characters. From MSDN:

A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).

Bill Tür stands with Ukraine
  • 3,425
  • 30
  • 38
  • 48
  • 1
    AKA, if you want the full output from a `(n)varchar(MAX)` containing 4000/8000 characters, use `SELECT` instead of `PRINT`. – Thom A Dec 06 '18 at 10:30