Why doesn't SQL Server's OBJECT_ID(NVARCHAR) take a variable?

Question

So why doesn't this work?

<!-- language: lang-sql -->
    DECLARE @tn NVARCHAR
    SET @tn = 'MyTable'
    SELECT OBJECT_ID(@tn)

When this does:

<!-- language: lang-sql -->
    SELECT OBJECT_ID('MyTable')

I need to pass a variable into this function.

Answer 1

It does.

declare @name sysname = N'sys.objects';
select object_id(@name);


-----------
-385

(1 row(s) affected)

In your example you declare a variable of length 1 (since you omit the length). OBJECT_ID(N'M') finds nothing and returns NULL.

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Answer 2

Your problem is that you're missing the size on your NVARCHAR.

This works:

DECLARE @tn NVARCHAR(20)
SET @tn = 'MyTable'
SELECT OBJECT_ID(@tn)