Wednesday, June 14, 2006

SqlCommand Parameters

Today I've lost quiet some time looking for an error in an assembly, which was loaded into the MS SQL Server 2005 as CLR user defined function.

When creating SQLCommands, I usually set something like SELECT * FROM [Table] WHERE [Field] = @Field as CommandText. Later in my code I declare the parameters I need like cmd.Parameters.Add('Field', SqlDbType.NChar);. Until today, I never had a problem using this approach. I knew, however, that I could have written cmd.Parameters.Add('@Field', SqlDbType.NChar); and it simply worked the same way. Since I didn't see any need to repeat that @, I didn't do it, ever (for sake of consistency). Until today...

Using C# 2.0, the first version works the same in any assembly, until(!) you load this assembly into the SQL Server 2005 and use it as CLR user defined function. Then it'll blow with a message saying: Incorrect syntax near 'Field'. At this point you'll need to include that @, when declaring the parameter.

I don't know why it behaves differently at this point, but I do know, that I'll include it from now on in any place it comes, until...


Post a Comment

<< Home

Search WWW Search