XML Feeds
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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.