Why Does SQL Error When I Forget the @ on Variable Passed a Procedure Parameter?

Question

I had a typo of missing an @ from a use of a variable. Normally SQL will compain about an undeclared variable. However in the case where it is used a sproc parameter sql server doesn't complain. It simply infers some singe quotes and moves on.

Can anyone explain why example C does not error?

--Ex A. Error
SELECT id

--Ex B. Error
CREATE FUNCTION dbo.fnTest (
  @id NVARCHAR(4)
) RETURNS NVARCHAR(4) AS BEGIN 
  RETURN @id 
END
GO
DECLARE @id NVARCHAR(4) 
SET @id= 'bob'
SELECT dbo.fnTest(@id) --missing my @, this should be @id
--SELECT dbo.fnTest(id) --missing my @, this should be @id
GO
DROP FUNCTION fnTest

--Ex C. No Error
CREATE PROC spTest (
  @id NVARCHAR(4)
) AS 
  SELECT @id
GO
DECLARE @id NVARCHAR(4) 
SET @id= 'bob'
EXEC spTest id --missing my @, this should be @id
GO
DROP PROC spTest

Answer 1

The same reason this has always worked:

EXEC sp_who2 active;

Versus the expected:

EXEC sp_who2 N'active';

Stored procedures are able to accept an input string without single quotes and still treat it as a string, as long as it doesn't have any special characters like spaces or dashes.

Why does it work for procedures and not for functions?

Doubt you'll get a satisfactory answer to that question, unless you can find the developers who worked on very early versions of SQL Server (well, probably Sybase) and buy them a beer and coax it out of them. Just one of those little implementation details that is "that's the way it works; there is no why." Most likely the two code paths were written by different developers, in a time where ship dates were prioritized over consistency. (Oh, wait a minute.)

---

PS you should always name your parameters, and you should always use the schema prefix when creating or referencing objects, e.g.

EXEC dbo.spTest @id = @id;

This prevents you from the typo you came across and also protects you from underlying changes to the stored procedure (e.g. someone sticks a new, optional parameter at the beginning of the parameter list).

Answer 2

Like any interpreter, TSQL sometimes makes assumptions instead of generating an error.

Lets take a look at what is going in the SQL Engine by doing a trace.

The two characters id are passed to the stored procedure as a parameter. It is type casted to a string and returned as the result. This is because EXEC call is just looking for strings to interpret into code, it does not generate an error.

Basically late binding.

Now, lets do a trace on the SELECT call to a function. The engine knows that it is a SELECT statement. Because you can only pass variables or columns to a function, it notices that there is no @, so it is not a variable. Thus, it must be a column. But since there is no from clause, it raises an error.

Basically early binding.