Originally posted on: http://geekswithblogs.net/meshel/archive/2005/02/20/23929.aspx
Back to my work woes...
Thanks to very helpfull people here (My friend from afar - Thanks!) and in SQLServerCentral (milzs) and some SQL Profiling... I found out the solution to this issue.
It all depends on how you actually call the stored procedure. Up until now we used to do first create the full command object (with the append method), and only then connect to the db for executing. In this method you have to specify data about your parameters including type (which was the problem). However, if you connect to the DB (assign connection string value to the ActiveConnection property) with the name of the SP already set. The Command object performs the following SP on the DB
exec [dbo]..sp_procedure_params_rowset N'isp_TestUpdateUserZip', 1, NULL, NULL
Which returns back a rowset detailing the exact parameters and data type of them, from theat point on - there is no need to specify explicitly any additional info besides the Parameter name and value. This behavior can be explicitly invoked by using the cmd.Parameters.refresh
Another interesting point is where you get the error (in case parameters don't fit the spec), In the first scenario - only when you execute the command, in the second - when you try to set the bad value.
After knowing what to look for MSDN offers this helpful article which also explains about the round trip, and the additional issues that this approach helps resolve. So now I have a complete answer and possible solution to a nagging issue. The only problem remaining with this solution, which is not that serious is that in order to modify a type you need to drop it first, and you can't drop it before droping all dependant objects........ Actually this is a serious issue, meaning that complex (but possible) scripts need to be built for each such occasion - maybe this is a good point why not to use this solution in the first place...
This restriction is very sensible, for example think of what would happen if you change a UDT iudt_xxx nvarchar(4000) to int? this can't be done, without rebulding any table using that User-Defined Type (not to mention indexes and functions relying on the type). Probably should have thought about this before... ;-(