score:3

Accepted answer

One common reason is that stored procedures have notoriously weak metadata, particularly for complex code with branches etc. It is very hard for the external code to really understand what is being selected/returned.

A common trick is to replace the sproc with something mind-numbingly simple (i.e. that simply returns any sample data of the desired schema) for the process of inspection, and then swap back to the real code. Alternatively, if possible consider swapping to a UDF - as UDFs have much stronger schema metadata.

Of course, you could also hand-edit the DBML based on something similar that works correctly.

As an aside - you may have a number of issues with that existing code, including (but not limited to) DML/DDL transitions (forces recompile), multiple updates to the same table (forces selective recompile) - not to mention the cursor usage (which may of may not be necessary). Table variables (@table rather than #table) may be useful too. I'm not offering to re-write it; just note some things you might want to look at.

score:-1

The first SELECT in your SPROC is yielding an INT into the temporary variable

SELECT @CONTRACT = CONTRACT FROM [tbl_property] WHERE [PROPREF] = @PROPREF

which is what is tripping up the metadata. You should do a

 SET @CONTRACT = (SELECT CONTRACT FROM [tbl_property] WHERE [PROPREF] = @PROPREF)

instead.


Related Articles