SqlCommandBuilder.DeriveParameters is a helper function that returns the parameters used by a stored procedure, a bit like the old ADO Command.Refresh. However, there is a problem with it. Consider the following stored procedure…
Create Proc Bob.MyProcTest(@Name nvarchar(100))
as
select ‘hello’ + @Name
and…
Create Proc Fred.MyProcTest(@Name nvarchar(100))
as
select ‘hello’ + @Name
So we’ve got the same procedure differing only by the owner. When you ask SqlCommandBuilder.DeriveParameters to fetch the parameters it incorrectly returns 4 parameters rather than two (remember that the return value is counted as a parameter). It’s fairly obvious why but it shouldn’t do it and can, as I’ve found to my cost, easily cause problems.
[Edit] I’ve since been informed that if you prefix the stored procedure name with the owner it works fine. It does, and that’s certainly better than nothing, but I still believe returning an appended result could never be the correct the answer. SqlCommand.DeriveParameters is there to aid in constructing calling code so I believe it should always make useful answers, so if the params match it should only return one, if they’re is more than one possible set it should raise an exception and ask you to be more specific.
IT Bytes 