netTiers and stored procedures

Want to see what netTiers sees when it determines what return type should be for a stored procedure?

It uses FMTONLY ON which returns the metadata on the results if the stored procedure was run.

netTiers bases return type from stored procedures based on the results of running FMTONLY ON with the stored procedure passing null to all values. The problem with this approach is that all branches are run. This means if you have an if/else where two selects are run you will get a DataSet return type, even if both branches return all the fields needed for the base type. This is because two sets of data is returned.
So, if you want to see what netTiers sees try:

SET FMTONLY ON;
EXEC [dbo].[_Plan_GetByUserId] null
SET FMTONLY OFF;

where _Plan_GetByUserId is your stored procedure and all parameters that are passed in are null.

Some of the other problems with this is that when the stored proc is run between FMTONLY the permissions it has to run are severely restricted because it is run as a user with the membership public. You can’t create temp tables due to this permissions restriction as well as other issues. Additionally you’ll get a void method for the get if your stored proc errors out on one of the parameters being null.

Leave a Reply