Problem with SqlCommand.DeriveParameters

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))


select ‘hello’ + @Name



Create Proc Fred.MyProcTest(@Name nvarchar(100))


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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s