November 26, 2007 at 3:19 pm
I have a scenario where a temporary table is generated in a stored procedure based on an input parameter. If the value of the parameter is true the query needs to get data from an archive table (Table A). If the value is false the query needs to use (Table B).
Following the creation of the temp table, queries will then return result sets from data from the temp table.
I tried to name my temp table the same name for either condition but got the following error:
Msg 2714, Level 16, State 1, Procedure USP_CONDITIONAL_TEMPTABLE, Line 24
There is already an object named XXXXX in the database.
This shouldn't be causing an error because the table creation statements are part of the IF Statement so only one temp table could possibly get generated per execution of the stored proc.
I know I can easily create a separate proc to query TABLE A and one to query TABLE B and let the business logic determine which proc to run.
My question is, is there a way to have SQL not validate a query? Logically the temp table wouldn't ever get created twice.
Here is an example using the Adventure Works Database
/* USES ADVENTURE WORKS DATABASE
Create Terminated Employee Table
SELECT *
INTO HumanResources.Employee_Terminated
FROM HumanResources.Employee
GO
*/
CREATE PROCEDURE USP_CONDITIONAL_TEMPTABLE
(@Terminated BIT)
AS
BEGIN
IF @Terminated = 1
BEGIN
SELECT EmployeeID, VacationHours, SickLeaveHours, HireDate
INTO #EmpTable
FROM HumanResources.Employee_Terminated
END
ELSE
BEGIN
SELECT EmployeeID, VacationHours,SickLeaveHours, HireDate
INTO #EmpTable
FROM HumanResources.Employee
END
-- Result Set
SELECT EmployeeID, Hire_Date
FROM #EmpTable
Order by HireDate DESC
DROP TABLE #EmpTable
END
November 26, 2007 at 3:51 pm
SQL is parsed. Then it is executed. The parser is generating the error, and that is happening before the IF statement has executed. The parser simply sees 2 attempts to create the same table - nothing has executed yet to let it determine that 1 of the branches won't actually execute.
Create the table first, before the IF statement, then populate it using INSERT instead of SELECT INTO.
Alternatively, populate using mutually exclusive UNION'ed selects and dispense with the IF
SELECT EmployeeID, VacationHours, SickLeaveHours, HireDate
INTO #EmpTable
FROM HumanResources.Employee_Terminated
WHERE @Terminated = 1
UNION ALL
SELECT EmployeeID, VacationHours,SickLeaveHours, HireDate
FROM HumanResources.Employee
WHERE @Terminated <> 1
November 27, 2007 at 3:51 pm
Thanks for showing me a couple of other ways to do this. I particularly liked the UNION example to avoid using IF. I can already see a use for that in other queries I will be writing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply