TBH I've never really looked at how TDS (the SQLServer wire protocol) works. From a code level, in ADO.NET you would create a query object, set the type to "sproc", set the name of the sproc, and attach parameters as objects. I bet the sproc name is going across as text, and maybe the param names, but that's probably it.

>>So you've got to figure out a release and deployment process that handles both, and a lot of shops don't seem to bother.

Yeah, that's true. Setting up something along the lines of Rails' migrations isn't really that hard, but many don't bother.