Stored Procedure Craziness

Classic Servoy
1

I have a stored procedure that was working, but was edited to add some fields and now returns no dataset to Servoy. However, it returns a dataset in Query Analyzer as both versions. Help? The first version, which works:

CREATE PROCEDURE dbo.ud_BudgetContractSelect
      @proj_id INTEGER,
      @tier2_id INTEGER, 
      @tier3_id INTEGER,
      @sortorder VARCHAR(30)
   AS

-- Create Procedure ud_BudgetContractSelect
-- retrieve the distinct values of contract_id for base contracts on budget rows
-- ( base contract rows have the parent id = to the contract id )
-- UNION'ed with rows attached to amendments and the contract is not on any other budget row
-- for this proj_id, tier2_id, tier3_id
-- 
-- PARAMETERS IN:
-- proj_id		PK of TblProj01Project
-- tier2_id		PK of TblProj07Tier2
-- tier3_id		PK of TblProj09Tier3
-- sortorder   contract_nbr and start_date are the only 2 values
-- by contract_nbr is the default
-- PARAMETERS OUT:
-- none
-- FUNCTION CALLS
-- udfBTLContractSelect
-- Last Change Date and Description
-- Jan    2004 written by Vicki Cowles-CS3W Associates Inc.
-- Jan 15,2004 add ETC column 
-- Apr 15,2004 add milestone col, remove ETC, change EAC to ETC/EAC
-- Apr 22,2004 remove budget line data (minstart, max end, total dollars) 
-- and replace with contract info (contract total dollars, ntpdate, actuals)
-- Jun 17,2004 split the etc/eac columns into 2. eac is once agan the calculated column
-- oct 13,2004 add 4th parm to call of udfBTLContractSelect. target_id = 0
-- jan 05,2005 change selects to be just the fields for the servoy app

-- --	test lines
-- 	DECLARE @proj_id int, @tier2_id int, @tier3_id INTEGER, 
-- 		@sortorder VARCHAR(30)
-- 	SET @proj_id = 134  -- 282
-- 	SET @tier2_id = 22  -- 19
-- 	SET @tier3_id = 0  -- 0
-- 	SET @sortorder = 'start_date'
-- --	end test lines 

   DECLARE @target_id INT
   SET @target_id = 0  -- this tells the function to get current budget rows, not saved target rows


   IF @sortorder = 'start_date'
      SELECT
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         '1/1/2006' AS min_start,
         '12/31/2010' AS max_end,
         25000 AS ETC
      FROM dbo.udfBTLContractSelect ( @proj_id, @tier2_id, @tier3_id, @target_id )
      ORDER BY ntp_date, contract_nbr
   ELSE
      SELECT
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         '1/1/2006' AS min_start,
         '12/31/2010' AS max_end,
         25000 AS ETC
      FROM dbo.udfBTLContractSelect ( @proj_id, @tier2_id, @tier3_id, @target_id )
      ORDER BY status DESC, contract_nbr,  ntp_date


GO

The second version, which works in Query Analyzer but gives no error and no dataset to Servoy:

CREATE PROCEDURE dbo.ud_BudgetContractSelect
      @proj_id INTEGER,
      @tier2_id INTEGER, 
      @tier3_id INTEGER,
      @sortorder VARCHAR(30)
   AS

-- Create Procedure ud_BudgetContractSelect
-- retrieve the distinct values of contract_id for base contracts on budget rows
-- ( base contract rows have the parent id = to the contract id )
-- UNION'ed with rows attached to amendments and the contract is not on any other budget row
-- for this proj_id, tier2_id, tier3_id
-- 
-- PARAMETERS IN:
-- proj_id		PK of TblProj01Project
-- tier2_id		PK of TblProj07Tier2
-- tier3_id		PK of TblProj09Tier3
-- sortorder   contract_nbr and start_date are the only 2 values
-- by contract_nbr is the default
-- PARAMETERS OUT:
-- none
-- FUNCTION CALLS
-- udfBTLContractSelect
-- Last Change Date and Description
-- Jan    2004 written by Vicki Cowles-CS3W Associates Inc.
-- Jan 15,2004 add ETC column 
-- Apr 15,2004 add milestone col, remove ETC, change EAC to ETC/EAC
-- Apr 22,2004 remove budget line data (minstart, max end, total dollars) 
-- and replace with contract info (contract total dollars, ntpdate, actuals)
-- Jun 17,2004 split the etc/eac columns into 2. eac is once agan the calculated column
-- oct 13,2004 add 4th parm to call of udfBTLContractSelect. target_id = 0
-- jan 05,2005 change selects to be just the fields for the servoy app
-- jan 25,2005 add the minstart, maxend, and sum(etc) for the budget rows in this contract
--             also return all the 'other' rows in this proc, grouped by the field calcothergroup

-- --	test lines
-- 	DECLARE @proj_id int, @tier2_id int, @tier3_id INTEGER, 
-- 		@sortorder VARCHAR(30)
-- 	SET @proj_id = 134  -- 282
-- 	SET @tier2_id = 1  -- 19
-- 	SET @tier3_id = 0  -- 0
-- 	SET @sortorder = ''
-- --	end test lines 

   DECLARE @target_id INT, @ETC MONEY, @min_start AS DATETIME, @max_end AS DATETIME, 
   @i INT, @cnt INT, @contract_id INT

   DECLARE @tmpContract TABLE (
   tcid INT IDENTITY (1,1),
   contract_id INT,
   contract_nbr VARCHAR(20),
   contract_title VARCHAR(150),
   ntp_date DATETIME,
   curr_completion_date DATETIME,
   actuals_LTD MONEY default 0,
   orig_amt MONEY default 0,
   status INT,
   min_start DATETIME,
   max_end DATETIME,
   ETC MONEY default 0,
   calcothergroup INT default 0    )

   SET @target_id = 0  -- this tells the function to get current budget rows, not saved target rows

   INSERT INTO @tmpContract (
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         status     )
      SELECT
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         status  
      FROM dbo.udfBTLContractSelect ( @proj_id, @tier2_id, @tier3_id, @target_id )

   SELECT @cnt = count(*) FROM @tmpContract
   SET @i = 1
   WHILE @i <= @cnt
   BEGIN
      SELECT @contract_id = contract_id FROM @tmpContract WHERE tcid = @i

      SELECT   @min_start = MIN(start_date),
               @max_end   = MAX(finish_date),
               @ETC       = SUM(current_forecast)  
      FROM ViewBudgetTierLine  
      WHERE  proj_id        = @proj_id  
            AND tier2_id    = @tier2_id 
            AND tier3_id    = @tier3_id 
            AND contract_id = @contract_id

      UPDATE @tmpContract
      SET   min_start = @min_start,
            max_end = @max_end,
            ETC = @ETC
      WHERE tcid = @i

      SET @i = @i + 1
   END

-- now get the 'other' rows to show as 'contracts'
   INSERT INTO @tmpContract (
         contract_id,
         contract_nbr,
         contract_title,
         min_start,
         max_end,
         ETC,
         calcothergroup              )
      SELECT
         contract_id,
         'TEMP:' + contract_nbr,
         contract_desc,
         MIN(Start_date) AS min_start,
         MAX(end_date) AS max_end,
         SUM(budget) AS ETC,
         calcothergroup
      FROM TblContractPlan01Budget
      WHERE proj_id        = @proj_id  
            AND tier2_id    = @tier2_id 
            AND tier3_id    = @tier3_id 
            AND contract_id = 0
      GROUP by proj_id,tier2_id,tier3_id, contract_id, contract_nbr, contract_desc, calcothergroup

   SELECT   contract_id,
            contract_nbr,
            contract_title,
            ntp_date,
            curr_completion_date,
            actuals_LTD,
            orig_amt,
            min_start,
            max_end,
            ETC,
            calcothergroup
   FROM @tmpContract
   ORDER BY status DESC, contract_nbr,  ntp_date




GO

HELP? Servoy bug?

ellen

2

what is the servoy code how you call that?

can i call it somewhere?
Because i can’t use that proc just at my place because i need some tables and data.