SQL trigger: Change nvarchar size

Question

I have several tables with columns nvarchar(max). I am building a trigger which tries to store the cells in sql_Variant. Nvarchar(max) cannot be stored in sql_variant. How I can check if it is nvarchar(max) and then convert it to nvarchar(4000) so I can avoid this?

EDIT: Most probably the error occurs here:

set @sql = 'select @audit_oldvalue=[' +@Item +'] from #tempTrigT';

Because @audit_oldvalue is sql_variant but [' +@Item +'] from #tempTrigT can be nvarchar(max)

If I edit something in the input table I get this error:

operand type clash nvarchar(max) is incompatible with sql_variant

Tables Structure:

input table

output table

Code where I want to convert:

Select * into #tempTrigT from (select * from deleted where @Action in ( 'U','D')) A UNION (select * from inserted where @Action ='I') 

Select @sql = @sql + 'Case when IsNull(i.[' + Column_Name +  
'],0) = IsNull(d.[' + Column_name + '],0) then ''''  
 else ' + quotename(Column_Name, char(39)) + ' + '',''' + ' end +' 
from information_schema.columns  
where table_name = 'Forms' and column_name <>'rowguid' and column_name <>'modifieddate'
--Define output parameter 
set @ParmDefinition = '@OutString varchar(max) OUTPUT' 
--Format sql 
set @sql = 'Select @OutString = '  
+ Substring(@sql,1 , len(@sql) -1) +  
' From dbo.Forms i  ' --Will need to be updated for target schema 
+ ' inner join #tempTrigT d on 
i.id = d.id'  --Will need to be updated for target schema 
--Execute sql and retrieve desired column list in output parameter 
exec sp_executesql @sql, @ParmDefinition, @OutString OUT


DECLARE @Items VARCHAR(max)

 set @Items = @OutString;

 DECLARE @Item VARCHAR(50)
 DECLARE @Pos INT
 DECLARE @Loop BIT
 SELECT @Loop = CASE WHEN LEN(@OutString) > 0 THEN 1 ELSE 0 END
 WHILE (SELECT @Loop) = 1
 BEGIN
 SELECT @Pos = CHARINDEX(',', @OutString, 1)
 IF @Pos > 0
 BEGIN
 SELECT @Item = SUBSTRING(@OutString, 1, @Pos - 1)
 SELECT @OutString = SUBSTRING(@OutString, @Pos + 1, LEN(@OutString) - @Pos)
----------------------------------

set @audit_field = @Item;

set @sql = 'select @audit_oldvalue=[' +@Item +'] from #tempTrigT'; --ERROR LINE
EXEC SP_EXECUTESQL @sql,N'@audit_oldvalue sql_variant OUTPUT',@audit_oldvalue OUTPUT -- If inserted @audit_oldvalue gets the new value

set @sql = 'select @audit_value=i.[' +@Item +'] from dbo.Forms i  inner join #tempTrigT d on i.id = d.id';
EXEC SP_EXECUTESQL @sql,N'@audit_value sql_variant OUTPUT',@audit_value OUTPUT


if @Action = 'U'
begin
    insert into [dbo].[AuditTrailForms]([TSid],[TSField],[OldValue],[NewValue],[changedate],[Change_Action],[Change_user],[Columns_Updated])
    select id,@audit_field, @audit_oldvalue, @audit_value,getdate(),@Action, coalesce(ModifiedBy,suser_name()), @Items
    from inserted 

end

     END
 ELSE
 BEGIN
 SELECT @Item = @OutString
 SELECT @Loop = 0
 END
 END

Look: https://stackoverflow.com/questions/626899/how-do-you-change-the-datatype-of-a-column-in-ms-sql — Murat Özcan, Sep 06 '17 at 14:31
@MuratÖzcan I dont want to alter the tables. I want to convert the value of the nvarchar(max) column and store it in sql_variant — aggicd, Sep 06 '17 at 14:33

PreQL · Answer 1 · 2017-09-07T09:19:24.040

1

You should be able to just cast it as VARCHAR(4000).

Select @sql = @sql + 'Case when IsNull(CAST(i.[' + Column_Name +  
'] AS VARCHAR(4000)),0) = IsNull(CAST(d.[' + Column_name + '] AS VARCHAR(4000)),0) then ''''  
 else ' + quotename(Column_Name, char(39)) + ' + '',''' + ' end +' 
from information_schema.columns  
where table_name = 'Forms' and column_name <>'rowguid' and column_name <>'modifieddate'

Or if you want to only do this for those columns (I don't believe it would an issue unless for performance reasons).

Select @sql = @sql + 'Case when IsNull(' 
    + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'CAST(' ELSE '' END 
    + 'i.[' + Column_Name +  ']' 
    + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN ' AS VARCHAR(4000))' ELSE '' END + 
    ',0) = IsNull('
    + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'CAST(' ELSE '' END 
    + 'd.[' + Column_name + ']'
    + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN ' AS VARCHAR(4000))' ELSE '' END + 
    + ',0) then ''''  else ' 
    + quotename(Column_Name, char(39)) + ' + '',''' + ' end +' 
from information_schema.columns  
where table_name = 'Forms' and column_name <>'rowguid' and column_name <>'modifieddate'

edited Sep 07 '17 at 09:19

answered Sep 06 '17 at 22:18

PreQL

358
2
6

OP has posted questions regarding this piece of code a number of times albeit different questions but has not accepted an answer in any thus far (that I've seen). Not sure if OP is clear on what is actually required. (Apologies if not in right place, not enough rep so can only comment on my own posts) – PreQL Sep 06 '17 at 22:21
thank you for you answers. I made different questions because there were in the same piece of code but different topic. Of course I have accepted questions. – aggicd Sep 07 '17 at 07:22
I tested the 2nd snippet but I receive the same error again. I dont want to convert everything in VARCHAR(4000) thats why I chose snippet #2 – aggicd Sep 07 '17 at 08:49
Nothing. But I guess now that the error occurs in this line: set sql = 'select @audit_oldvalue=[' +Item +'] from #tempTrigT'; EXEC SP_EXECUTESQL sql,N'@audit_oldvalue sql_variant OUTPUT',@audit_oldvalue OUTPUT Where @Item contains the column that change occured. so it fetches the Item column from tempTrigT which is nvarchar(max) and stores it sql_variant – aggicd Sep 07 '17 at 09:27
What is tempTrigT? The creation of that table not included in your post – PreQL Sep 07 '17 at 10:23
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/153859/discussion-between-aggicd-and-preql). – aggicd Sep 07 '17 at 10:28

SQL trigger: Change nvarchar size

1 Answers1