A question was posted in our SQL Server programming forum today. A person had this stored procedure

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.

value

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

SELECT CONVERT(VARCHAR,'aaaaa')

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?

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum