CREATE PROCEDURE TestStuff @id INT, @Val1 VARCHAR(20), @Val2 VARCHAR(20) AS SELECT @id,@Val1,@Val2
Executing it like this works
EXEC TestStuff 1,'test1','Test2'
However executing it like this also works
EXEC TestStuff 1,test1,Test2
So even though you don’t enclose the character value in quotes it works.
From the SQL Server 2008 Books Online (June 2009) EXECUTE (Transact-SQL) page, I changed the color to red for the sentence that is wrong.
Is the value of the parameter to pass to the module or pass-through command. If parameter names are not specified, parameter values must be supplied in the order defined in the module.
When executing pass-through commands against linked servers, the order of the parameter values depends on the OLE DB provider of the linked server. Most OLE DB providers bind values to parameters from left to right.
If the value of a parameter is an object name, character string, or qualified by a database name or schema name, the whole name must be enclosed in single quotation marks. If the value of a parameter is a keyword, the keyword must be enclosed in double quotation marks.
If a default is defined in the module, a user can execute the module without specifying a parameter.
The default can also be NULL. Generally, the module definition specifies the action that should be taken if a parameter value is NULL.
As you can see that is not right at all, I checked Books on line for SQL Server 2000 and it basically has the same information. I am surprised that nobody has alerted Microsoft about this.
So now that we know that you can do that, then why can you not do this?
DECLARE @v VARCHAR(20) SELECT @v = a SELECT @v
That gives you this error
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name ‘a’.
Of course there are other inconsistent things in SQL Server, here is a perfect example
Varchar defaults to 1 character here
DECLARE @v VARCHAR SELECT @v = 'aaaaaa' SELECT @v
Varchar defaults to 30 characters here
George Mastros wrote a nice blog post about this here: SQL Server Stored Procedure with nvarchar parameter
So what do you think, should I file an item for this on connect?