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

Question

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;

There is no variable assignment in @sql… `set @sql = CONCAT('select @object_id=OBJECT_ID(N'''`..and be careful of injection — lptr, Mar 24 '23 at 12:12
SQL *Server* Management Studio (SSMS) not "SMS" (that's a text message). — Thom A, Mar 24 '23 at 12:15
Instead of using dynamic sql..just use OBJECT_ID()..`select @object_id = OBJECT_ID(@table_name, N'U')` — lptr, Mar 24 '23 at 12:26
lptr: Not sure how to select your comment as the answer!.. but it was! — Velocedge, Mar 24 '23 at 12:30
Why bother with dynamic SQL? I think you can simply assign the `OBJECT_ID` function result to your variable: `SET @object_id = OBJECT_ID(@table_name, 'U');` — Dan Guzman, Mar 24 '23 at 14:45
As a side note, if you really want to use dynamic SQL you should use `QUOTENAME` to escape your object names — Charlieface, Mar 24 '23 at 16:05
You should post the answer as an actual answer below, not as part of the question. You are free to answer your own question. — Charlieface, Mar 25 '23 at 20:27

score 1 · Accepted Answer · answered Mar 25 '23 at 20:28

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.

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

1 Answers1

Linked