Why does OBJECT_ID return NULL in a computed column?

I’m trying to produce a usage report for some stored procedures in my database. The view view sys.dm_exec_procedure_stats contains some of what I need, so I decided to start here.

SQL Server keys procedure metadata by object ID. I know only the procedure names, so first I have to map the names to IDs using the OBJECT_ID function.

There are several procedures to check, so I want to put their names in a temp table before processing them.

I thought it would be cute to get the object id from a computed column in the temp table, so I created a table like this:

USE msdb;
GO

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('dbo.sp_add_job');
INSERT INTO #procs (name) VALUES ('dbo.sp_start_job');

With that table in place, the obvious way to get the execution counts looked like this:

SELECT #procs.name, execution_count
FROM sys.dm_exec_procedure_stats AS procstats
INNER JOIN #procs ON #procs.[object_id] = procstats.[object_id]
ORDER BY execution_count DESC;

But it returns an empty result set!

This query shows why the result set is empty:

SELECT name, [object_id]
FROM #procs;

The object_id column is NULL, so an inner join will produce no rows:

name                 object_id
-------------------- -----------
dbo.sp_send_dbmail   NULL
dbo.sp_add_job       NULL
dbo.sp_start_job     NULL

It’s not that computed columns are broken for temp tables. If I add a computed column to reverse the name, it produces the correct value when I select from it:

ALTER TABLE #procs ADD reverse_name AS REVERSE(name);

SELECT name, [object_id], reverse_name
FROM #procs;

Result:

name                 object_id   reverse_name
-------------------- ----------- ---------------------
dbo.sp_send_dbmail   NULL        liambd_dnes_ps.obd
dbo.sp_add_job       NULL        boj_dda_ps.obd
dbo.sp_start_job     NULL        boj_trats_ps.obd

Why does OBJECT_ID return NULL here?

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054

You are doing overly overly overly complicated something trivial. Remove the computed column, insert OBJECT_ID explicitly.

What happens is that you’re lossing control over where is OBJECT_ID evaluated (in which database context). OBJECT_ID will return different values, for same name, in different DBS, or may return NULL for names that do not match current execution context (db). By adding the evaluation onto a #temp table you can’t be sure where does the evaluation occur. Is it in tempdb? in msdb? In current db? Well, it looks like is not where you expected it to be, that’s sure…

Ditch the fancy cleverness, stick to KISS.

Next, fix your `JOIN #procs ON #procs.[object_id] = procstats.[object_id], as is incorrect. You need to join on object_id and database_id.