I wrote myself a stored procedure with dynamic SQL. No rocket science involved. Just a simple stored proc that strings up SQL and then used sqpExecuteSQL to being back some results into a temp table that I then select from. I ran a few qucik tests and all looked good so pointed CodeSmith at my database to generate a nice data access layer with the NetTeirs templates.
To my surprise the generated method for my custom stored procedure had a return type of void! In my experience if not all fields are returned in the select then CodeSmith won’t match that to a generated entity so instead of returning an entity from the generated method the method will return a dataset. But this time I got void!
So after a bit of mucking about I tried what I thought CodeSmith must be firing at the stored proc to get the types to build the entities and methods in the data access layer. That was exec StoreProcName null, null, null. I found my dynamic SQL was building correctly and throwing an error due to a logic bug in the stored proc in this scenario. So after fixing the logic bug I ran CodeSmith over it again and hey presto the return type on my method is the generated business entity as I would expect.
Makes perfect sense why the return type couldn’t be resolved. What suprised me was CodeSmith actually generated the method at all. I would have thought if a stored proc throws an error during generation that it would be excluded from the generated code base. Not sure if this is configurable, but something to watch out for.