EXEC sp_executesql doesn't work or doesn't return data

Question

I have code part from trigger. When I test my trigger my exec doesn't return any data.

I declared my exec value as:

declare
    @sql nvarchar(MAX), 
    @SQLString varchar(MAX)

and here is the select:

set @sql =  N'select ' 
        set @sql = @sql +   '@SQLString = convert(varchar(1000),d.' + @fieldname + ')' +' + convert(varchar(1000),i.' + @fieldname + ')'
set @sql = @sql +   ' from #ins i full outer join #del d'
set @sql = @sql +   @PKCols --
set @sql = @sql +   ' where i.' + @fieldname + ' <> d.' + @fieldname 
set @sql = @sql +   ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
set @sql = @sql +   ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)'

(all variables work fine): like if I print @sql it will like this ->

select @SQLString = convert(varchar(1000), d.ActionID) + convert(varchar(1000), i.ActionID) 
from #ins i 
full outer join #del d on i.ActionID = d.ActionID 
where i.ActionID <> d.ActionID 
   or (i.ActionID is null and d.ActionID is not null) 
   or (i.ActionID is not null and d.ActionID is null)

Here is execution:

EXEC sp_executesql @sql, 
        N'@SQLString varchar(MAX) OUTPUT', 
        @SQLString = @SQLString OUTPUT;

but print shows nothing:

print '@SQLString:' + @SQLString;

score 1 · Accepted Answer · answered Nov 30 '17 at 20:34

Investigate your select statement, from running this on some test data there was no cases returned where ActionID from both #ins & #del are non-null, one was always null for me.

Which leads to you adding a non-null and a null string, i.e.

convert(varchar(1000), d.ActionID) + convert(varchar(1000), i.ActionID)

Which then returns a null and @SQLString is null as a result.

Some test code I threw together:

CREATE TABLE #ins 
(
    ActionID INT
)

CREATE TABLE #del
(
    ActionID INT
)

INSERT INTO #ins
VALUES (1),(3),(5),(7),(9)

--Data sets mostly disjoint, have 1 overlapping value to see what happens.
INSERT INTO #del
VALUES (2),(4),(6),(8),(9),(10)

--Visual demo of return values.
SELECT * 
FROM #ins i 
     FULL OUTER JOIN #del d ON i.ActionID = d.ActionID 
WHERE i.ActionID != d.ActionID 
      OR (i.ActionID IS NULL AND d.ActionID IS NOT NULL) 
      OR (i.ActionID IS NOT NULL AND d.ActionID IS NULL)

--Original SQL to demonstrate NULL return value.
DECLARE @sql NVARCHAR(MAX) = 'SELECT @SQLString = CONVERT(NVARCHAR(1000), d.ActionID) + CONVERT(NVARCHAR(1000), i.ActionID) FROM #ins i FULL OUTER JOIN #del d ON i.ActionID = d.ActionID WHERE i.ActionID != d.ActionID OR (i.ActionID IS NULL AND d.ActionID IS NOT NULL) OR (i.ActionID IS NOT NULL AND d.ActionID IS NULL)'
       ,@SQLString NVARCHAR(MAX)

EXEC sp_executesql @sql, N'@SQLString NVARCHAR(MAX) OUTPUT', @SQLString = @SQLString OUTPUT

SELECT @SQLString

--Modified original vars/sql and wrapped in ISNULL to demonstrate non-null return value.
DECLARE @sql2 NVARCHAR(MAX) = 'SELECT @SQLString = CONVERT(NVARCHAR(1000), ISNULL(d.ActionID,0)) + ''-'' + CONVERT(NVARCHAR(1000), ISNULL(i.ActionID,0)) FROM #ins i FULL OUTER JOIN #del d ON i.ActionID = d.ActionID WHERE i.ActionID != d.ActionID OR (i.ActionID IS NULL AND d.ActionID IS NOT NULL) OR (i.ActionID IS NOT NULL AND d.ActionID IS NULL)'
       ,@SQLString2 NVARCHAR(MAX)

EXEC sp_executesql @sql2, N'@SQLString NVARCHAR(MAX) OUTPUT', @SQLString = @SQLString OUTPUT

SELECT @SQLString

score 0 · Answer 2 · answered Nov 30 '17 at 20:33

0

When using the '+' operator to concatenate a Null with a string, you get a Null.

Use the CONCAT function instead. It converts the arguments to strings for you and treats Null like empty string.

answered Nov 30 '17 at 20:33

David Browne - Microsoft

80,331
6
39
67

We use SQL 2008, and I think Concat is available from 2012. But thank you for your recommendation! – Ange Nov 30 '17 at 20:45

EXEC sp_executesql doesn't work or doesn't return data

2 Answers2