Why does storing a value from sp_executesql return NULL?

Question

Once more I stumble upon an SQL mishappening. The following code:

create trigger tc_trigger_olt_UPDATE on tick_orderline_type
for update as
declare @UserId varchar(32)     --
declare @ClientId varchar(32)   --
declare @CaseId varchar(32)     --
declare @TableName varchar(64)  --Used
declare @RecordId varchar(512)  --Used
declare @Descr varchar(128)     --
declare @RemoteIP varchar(16)   --Used

select @TableName   = object_name(parent_id) from sys.triggers where object_id = @@procid
select @RemoteIP    = client_net_address from sys.dm_exec_connections where Session_id = @@SPID
if exists(select * from information_schema.columns where table_name = @TableName and column_name = 'id')
begin
    select @RecordId = id from inserted
end

declare @sql nvarchar(max)
set @sql = 'select case_id from '+@TableName+' where id = '''+@RecordId+'''';

exec sp_executesql @sql, N'@out_param varchar(32) OUTPUT', @out_param=@CaseId OUTPUT

execute update_tick_orderline_type @UserId, @ClientId, @CaseId, @TableName, @RecordId, @Descr, @RemoteIP

Is used for filling and audit-table in SSMS, which works. It stores which table, which record, on which date and from which IP-Address the table has been edited.

I am currently trying to get which client's data, for which case, has been edited using the following snipped from above:

declare @sql nvarchar(max)
set @sql = 'select case_id from '+@TableName+' where id = '''+@RecordId+'''';

exec sp_executesql @sql, N'@out_param varchar(32) OUTPUT', @out_param=@CaseId OUTPUT

In my Results window (the one that shows select statement results), I can see that the @sql statement has selected the correct case_id; storing it in the @CaseId variable however, returns NULL. Once I have the @CaseId I can get the @ClientId, so I'm stumped here.

Why is the statement outputting the correct case_id, but stores it as NULL?

A little sidenote: the case_id is only being output when the exec statement is present, otherwise it is not

Answer 1

I managed to fix my own predicament, by changing the previous 'select x from @TableName' block, to this:

declare @sql nvarchar(max)
declare @params nvarchar(max)
set @params = N'@iCaseId varchar(max) output'
select @sql = N'select @iCaseId = case_id from '+quotename(@TableName)+
        ' where id = '''+@RecordId+''''

execute sp_executesql @sql, @params, @CaseId output

---

What changed?

• I declared an additional @params variable, for readability
• Instead of set I select-ed the @sql variable
• quotename now surrounds the @TableName variable
• instead of the last param for sp_executesql being @x = @y output I simply replaced it with @y output

Conclusion

After several tests (mainly removing some of the changes), I can conclude that the last point on that list was key to solving the issue.
With quotename removed from the statement, I still manage to save the result in a variable, and since the @param variable is not mandatory, but rather a personal preference, it also had no real significance.