Monday, May 28, 2007
Weird Behavior With Stored Procedure Parameters That Are Delimited With Brackets
I received a question in our forum yesterday that stumped (and still stumps) me. Here's the forum discussion on the problem: http://forums.learnsqlserver.com/SqlServerTopic115.aspx?m=217#217
Basically, what we're seeing here is that, when you use brackets to delimit a parameterized expression in a stored procedure/function, your stored procedure will compile but it can never execute unless you override it's value. If, however, you try the same steps with a more declarative SQL, then it works fine.
It's quite odd and I just don't get it. I understand that brackets are a delimiter but why, as a stored procedure parameter, do they explicitly convert the argument to NVARCHAR? Has anyone see this before?
Basically, what we're seeing here is that, when you use brackets to delimit a parameterized expression in a stored procedure/function, your stored procedure will compile but it can never execute unless you override it's value. If, however, you try the same steps with a more declarative SQL, then it works fine.
It's quite odd and I just don't get it. I understand that brackets are a delimiter but why, as a stored procedure parameter, do they explicitly convert the argument to NVARCHAR? Has anyone see this before?
Labels: sql 2005, sql server
