locked
Stored Procedure fields are not visible if two queries are used in it. RRS feed

  • Question

  • User246057125 posted

    A report is connected to a stored procedure. This stored procedure contains a single SQL statement. If I modify this stored procedure as below, the Field Explorer stops displaying the column names of the SELECT statements.

    How to make Field Explorer display the column values enclosed in the IF condition?

    ALTER PROCEDURE [dbo].[pheSch_SchemeSummaryForDistrict] 
    	-- Add the parameters for the stored procedure here
    	@pDistrict nvarchar(50),
    	@pSchemeType nvarchar(50),
    	@pComptetionDateFrom varchar(4) = null,
    	@pCompletionDateTo varchar(4) = null
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	IF (LTRIM(RTRIM(@pComptetionDateFrom)) = '' AND LTRIM(RTRIM(@pCompletionDateTo)) = '')
    		BEGIN
    			SELECT dist_nm, block_nm, schm_nm, population_2001, supply_source, yr_approval, sch_apr_amt, 
    				CONVERT(varchar(10),schm_waited_date,105) SchmWaitedDate, waited_amt, sch_apr_amt, exp_lastyear, 
    				adh_exp, exp_agnst_appamt, provided_water, percentprogress, 
    				CONVERT(varchar(10),workcompletiondate,105) WorkCompletionDate, 
    				CONVERT(varchar(10),schm_trans_date,105) SchmTransDate,CONVERT(varchar(15),sno) AS SerialNum
    			FROM tw_main WHERE dist_nm = @pDistrict AND schemetype = @pSchemeType;
    		END
    	ELSE IF (LTRIM(RTRIM(@pComptetionDateFrom)) <> '' AND LTRIM(RTRIM(@pCompletionDateTo)) <> '')
    		BEGIN
    			SELECT dist_nm, block_nm, schm_nm, population_2001, supply_source, yr_approval, sch_apr_amt, 
    				CONVERT(varchar(10),schm_waited_date,105) SchmWaitedDate, waited_amt, sch_apr_amt, exp_lastyear, 
    				adh_exp, exp_agnst_appamt, provided_water, percentprogress, 
    				CONVERT(varchar(10),workcompletiondate,105) WorkCompletionDate, 
    				CONVERT(varchar(10),schm_trans_date,105) SchmTransDate,CONVERT(varchar(15),sno) AS SerialNum
    			FROM tw_main WHERE dist_nm = @pDistrict AND schemetype = @pSchemeType 
    AND Year(CompletionDate) > 2005; END END

    Thursday, February 2, 2012 2:50 AM

All replies

  • User1983487377 posted

    you must have Else portion... means each case must return a specific Table

    Thursday, February 2, 2012 3:10 AM