[SOLVED] output variable from sp_executesql is always null, yet showing the value in SSMS

output variable from sp_executesql is always null, yet showing the value in SSMS

I'm trying to build a series of dynamic SQL calls based on the existence of a list of tables. I want to check if the table exists in the database and if so, update certain fields. However, when I run the following statement, @object_id is always null!

set @sql = CONCAT('select OBJECT_ID(N''',@table_name, ''', N''U'')')
exec sp_executesql @sql, N'@object_id int output', @object_id output;
if @object_id is not null
begin
...
end

However, in SQL Server Management Studio I'm actually seeing the real @object_id when there is an existing table and null when there isn't one like it should! What is wrong with my sp_executesql statement?

EDIT: Thanks to lptr I made the following change and it works.

set @sql = CONCAT('select @oi = OBJECT_ID(N''',@table_name, ''', N''U'')')
exec sp_executesql @sql, N'@oi int output', @object_id output;

Solution

As mentioned by @lptr and @DanGuzman in the comments:

Instead of using dynamic sql, just use OBJECT_ID() directly

select @object_id = OBJECT_ID(@table_name, N'U')

Obviously if you really did need to have dynamic SQL you can do it like this

set @sql = CONCAT('select @oi = OBJECT_ID(N', QUOTENAME(@table_name, ''''), ', N''U'')');
exec sp_executesql
  @sql,
  N'@oi int output',
  @oi = @object_id output;

Note the use of QUOTENAME to escape the object name correctly.