Detecting when .netTiers is interrogating a stored procedures

During code generation using the .netTiers templates the stored procedures will be called to determine their outputs. If the stored procedures don't return the correct results netTiers will only provide IDataReader as a return type rather than a strongly typed object.

To resolve this use the SQL user_name() function to determine if netTiers is calling the stored procedure and return data that will generate the correct structure.

-- Check if netTiers is trying to discover how this stored proc works and return a dummy record with the expected result set.
 IF user_name() IS null
 BEGIN
  -- Return dummy data to netTiers with the expected data types
  SELECT TOP 1 * FROM [dbo].[SampleView]

  SELECT 1 as TotalRowCount

  RETURN
 END

It will pay to check your custom stored procedure works with FMTONLY. NetTiers will call the stored procedure with FMTONLY on and null for all of the parameters.

SET FMTONLY ON;

EXEC dbo.custom_sp_MyTestSproc @param1 = NULL, @param2 = NULL, @param3 = NULL

SET FMTONLY OFF;

I found this caused issues when using temporary tables. I.e.:

Msg 208, Level 16, State 0, Procedure custom_sp_StoredProcedureName, Line 160
Invalid object name '#TheTempTable'.

If this is the case you can turn FMTONLY OFF in the in stored procedure before creating the temporary table and then back on again afterward. E.g.

-- ...
DECLARE @fmtonlyon bit
SELECT @fmtonlyon = 0
IF 1 = 0 
BEGIN
 --This block will only be reached when FMTONLY is ON
 SELECT @fmtonlyon = 1
 SET FMTONLY OFF
END
    
CREATE TABLE #TheTempTable
  ( 
   -- ...
  )

--USE test here as FMTONLY will be disabled
IF @fmtonlyon = 1 SET FMTONLY ON