varchar(max) field truncated when fetched into varchar(max) variable in cursor

Question

I have the following procedure where I am looping through a series of view_definitions to modify the source, then execute in a stored proc. The query returns the complete definition, but if it's over 8k bytes, it gets truncated when I fetch it into my cursor. I've tried changing all the varchar(max) to nvarchar(max), but that truncates at 4k. Any ideas? --create procedure dbo.AuditByBillingGroup(@GrpID as varchar(max)) as declare @VName as varchar(max), @VSQL as varchar(max), @AS as integer, @vSel as varchar(30), @IsDist as integer; declare @grpid as varchar(max) set @grpid = '0080700061' declare CurAudit cursor for --Query returns full view definition or over 16k bytes SELECT name AS ViewName, OBJECT_DEFINITION(object_id) AS ViewDef FROM sys.views where name = 'AuditProgramIntegrityMG' --in (select auditview from auditlist) OPEN CurAudit --Fetch only gets the first 8000 bytes FETCH NEXT FROM CurAudit INTO @VName, @VSQL

Answer 1

declare @VName as nvarchar(max), @VSQL as nvarchar(max), 
@AS as integer, @vSel as varchar(30), @IsDist as integer;
declare @grpid as nvarchar(max)
set @grpid = '0080700061'
declare CurAudit cursor for 

--Query returns full view definition or over 16k bytes
SELECT 
name  AS ViewName,  
OBJECT_DEFINITION(object_id) AS ViewDef
FROM  
 sys.views  
 where name = 'AuditProgramIntegrityMG'
 --in (select auditview from auditlist)

OPEN CurAudit  

--Fetch only gets the first 8000 bytes
FETCH NEXT FROM CurAudit INTO @VName, @VSQL
print @vname + '-' +@vsql
WHILE @@FETCH_STATUS = 0  
BEGIN  
    set @as = charindex('Select', @vsql)
    set @IsDist = charindex('Distinct', @vsql)
    set @vsql = right(@vsql, len(@vsql)-@as+1)

    set @vsql = replace(@vsql, 'ContraxxSummary2', 'ContraxxSummaryPreApprv')
    if @IsDist = 0  
    set @vsql = 'Select ' + '''' + @vname + ''' AuditName,' + right(@vsql, len(@vsql) - 7 )
    else 
    set @vsql = 'Select distinct ' + '''' + @vname + ''' AuditName,' + right(@vsql, len(@vsql) - 15)
    set @vsql = 'Select * from (' + @vsql + ') x where billing_group_id_tx = ''' + @GrpID + ''''

    exec (@vsql)
    FETCH NEXT FROM CurAudit INTO @vname, @vsql
END 

Answer 2

PRINT only returns the first 8000 characters for varchar datatypes or 4000 characters if the datatype is nvarchar.
I think you are getting all the definition in @VSQL. To check, instead of PRINTing the value, do
SELECT DATALENGTH(@VSQL)
That will show you the actual length in bytes of that value. (Remmember that nvarchar data has 2 bytes per character).

Tom

Answer 3

In order to evaluate the full value of @vsql, try some suggestions for a similar problem: https://learn.microsoft.com/en-us/answers/questions/195617/.

Answer 4

I would fetch the object_id of the view through the cursor, instead of the definition of the view:

DECLARE @objectId int;
DECLARE @VName nvarchar(4000);
DECLARE @VSQL nvarchar(max);
DECLARE CurAudit CURSOR FOR 
    SELECT object_id, name
    FROM sys.views  
    WHERE name IN ('V1', 'V2', 'V3');
OPEN CurAudit
FETCH NEXT FROM CurAudit INTO @objectId, @VName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @VSQL = OBJECT_DEFINITION(@objectId);

    -- Do something here

    FETCH NEXT FROM CurAudit INTO @objectId, @VName
END
CLOSE CurAudit;  
DEALLOCATE CurAudit;

Answer 5

Hi @Rick Wiker ,

Welcome to Microsoft Q&A!

In case you face any performance issue with cursor, you could consider to use below while loop statement instead.

declare @VName as nvarchar(max), @VSQL as nvarchar(max),   
 @AS as integer, @vSel as varchar(30), @IsDist as integer;  
 declare @grpid as nvarchar(max)  
 set @grpid = '0080700061'  
  
 SELECT IDENTITY(int,1,1) ID,  
 name  AS ViewName,    
 OBJECT_DEFINITION(object_id) AS ViewDef  
 into #temp  
 FROM    
  sys.views    
  where name in ('view1','view2，view3');  
  --in (select auditview from auditlist)  
      
declare @i int=1  
declare @max int  
select @max=count(*) from #temp  
  
while @i<=@max  
 BEGIN    
     set @VSQL=''  
     set @vname=''  
     select @VSQL=ViewDef,@vname=ViewName from  #temp where id=@i  
     set @as = charindex('Select', @vsql)  
     set @IsDist = charindex('Distinct', @vsql)  
     set @vsql = right(@vsql, len(@vsql)-@as+1)  
          
     set @vsql = replace(@vsql, 'ContraxxSummary2', 'ContraxxSummaryPreApprv')  
     if @IsDist = 0    
     set @vsql = 'Select ' + '''' + @vname + ''' AuditName,' + right(@vsql, len(@vsql) - 7 )  
     else   
     set @vsql = 'Select distinct ' + '''' + @vname + ''' AuditName,' + right(@vsql, len(@vsql) - 15)  
     set @vsql = 'Select * from (' + @vsql + ') x where billing_group_id_tx = ''' + @GrpID + ''''  
      
     exec (@vsql)  
     set @i=@i+1  
 END   

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.