none
Can you use Multiple Select Statements in a Stored Procedure?

    Question

  • Im currently using Stored Procedures to process a set of results, im also using temporary tables. Savings need to be summed and then grouped and ordered in 4 categories by: 1) subpolicy_name 2) building_name 3) floor_name 4) room_name Now this is not a problem my SQL all works well however at the moment i have multiple select statements, From my code below you will see that i have a number of select statements, if i execute the stored procedure it returns the results as it should, however i need to use MS Chart Controls to display my results.

    My PROBLEM is that when using chart controls it will always pick up the first select statement but cannot find the 2nd select statement in my stored procedure, for example if i try and assign building_name from the select statement(see code below) to the x axis in my chart control it gives a error "column cannot be found".  

    The problem is that at the moment I am using 4 separate stored procedures, one for each select statement and this works well, but im hoping i can do it in 1 it would be a lot easier as i need to do a number of different reports and will have a vast number of stored procedures by the end :(
    I know i could do it in one select statement

    Select subpolicy_name, building_name, floor_name, room_name, SUM(CO2_savings) As total_savings
    	From #CO2temp
    	group by subpolicy_name, building_name, floor_name, room_name
    	Order by subpolicy_name, building_name, floor_name, room_name
    	
    
    The problem with this is that i don't get a summed total for subpolicy_name, building_name, floor_name, room_name, what i mean is that i have duplicate rows for policy it may display policy1 a number of times and not have a summed total for policy 1. This is leading to duplicate rows being created in my reports as well.

    Thanks in advance would appreciate any help or advice :)

    This is my full Stored Procedure
    ALTER PROCEDURE dbo.SP_TEST_2
    
    AS
    
    /* Create temp table */
    	CREATE TABLE #CO2temp
    	(
    	pc_profile_id int,
    	shutdown_Time datetime NULL, 
    	hibernate_Time datetime NULL, 
    	sleep_Time datetime NULL, 
    	startup_Time datetime NULL,
    	status varchar(50),
    	subpolicy_name varchar(50),
    	building_name varchar(50),
    	floor_name varchar(50),
    	room_name varchar(50),
    	total_hrs_off int, 
    	pc_kwh_rate float,
    	CO2_factor float,
    	CO2_savings float
    	)
    	
    	
    	/** Insert Values into Temp Table from View_Savings_Report*/
    	
    	insert into #CO2temp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, floor_name, room_name, pc_kwh_rate, CO2_factor)
    	SELECT PC_PROFILE_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, SUB_POLICY_NAME, BUILDING_NAME, FLOOR_NAME, ROOM_NAME, PC_POWER_RATING, CO2_FACTOR
    	FROM VIEW_CO2_REPORT 
    	
    	/** Get total hours off*/
    	UPDATE #CO2temp
    	SET total_hrs_off = DATEDIFF(HOUR, 
    	COALESCE(shutdown_Time,hibernate_Time,sleep_Time,startup_Time),
    	COALESCE(startup_Time,sleep_Time,hibernate_Time,shutdown_Time))
    
    
    	/** Calculate the Savings in CO2 when system is in place, multiple hours off * KWH Rate * CO2 Factor*/
    	UPDATE #CO2temp
    	SET CO2_savings = (isnull(total_hrs_off, 0) * pc_kwh_rate * CO2_factor)
    	
    	
    	/** Getting Total Savings per Policy*/
    	Select subpolicy_name, SUM(CO2_savings) As total_savings
    	From #CO2temp
    	group by subpolicy_name
    	Order by subpolicy_name
     	
     	/** Getting Total Savings per Building*/
     	Select building_name, SUM(CO2_savings) As total_savings
    	From #CO2temp
    	group by building_name
    	Order by building_name
    	
    	/** Getting Total Savings per Floor*/
    	Select floor_name, SUM(CO2_savings) As total_savings
    	From #CO2temp
    	group by floor_name
    	Order by floor_name
    	
    	/** Getting Total Savings per Room*/
    	Select room_name, SUM(CO2_savings) As total_savings
    	From #CO2temp
    	group by room_name
    	Order by room_name
    
    
    	RETURN
    




    Wednesday, January 06, 2010 11:22 AM

Answers

  • insertinto #CO2temp SELECT PC_PROFILE_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, SUB_POLICY_NAME, BUILDING_NAME, FLOOR_NAME, ROOM_NAME, PC_POWER_RATING, CO2_FACTOR FROM VIEW_CO2_REPORT

    The Insert code above u can just make it as INSERT INTO rather than having the column name.Look at the above code with the changes.

    For the stored procedure my advice try to combine all the table using join statement and then try to sum it out.

    Vote me if its helpful.Thanks
    Thursday, February 28, 2013 7:01 AM

All replies

  • The question is really whether MS Chart suports mutiple resultsets from one batch (a proc is a batch, from a TSQL perspective). I suggest you ask this in a forum for MS chart, where it is more likely you will get good help...


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by Naomi NModerator Wednesday, January 06, 2010 2:15 PM
    • Unproposed as answer by NT86 Wednesday, January 06, 2010 2:53 PM
    Wednesday, January 06, 2010 1:31 PM
  • insertinto #CO2temp SELECT PC_PROFILE_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, SUB_POLICY_NAME, BUILDING_NAME, FLOOR_NAME, ROOM_NAME, PC_POWER_RATING, CO2_FACTOR FROM VIEW_CO2_REPORT

    The Insert code above u can just make it as INSERT INTO rather than having the column name.Look at the above code with the changes.

    For the stored procedure my advice try to combine all the table using join statement and then try to sum it out.

    Vote me if its helpful.Thanks
    Thursday, February 28, 2013 7:01 AM