debugsqlMinor
if block fails to create temp table in a procedure
Viewed 0 times
failscreateblocktempproceduretable
Problem
I'm attempting to do this in a procedure:
I get the error:
Why is this happening and is there a work around?
Update
I've attempted to add a
DECLARE @a bit = 1;
BEGIN
SELECT * INTO #aTemp FROM OPENROWSET( ... );
IF @a = 0
BEGIN
SELECT ... INTO #bTemp FROM #aTemp;
END
ELSE
BEGIN
SELECT ... INTO #bTemp FROM #aTemp;
END
ENDI get the error:
Msg 2714, Level 16, State 1, Line 10
There is already an object named '#bTemp' in the database.Why is this happening and is there a work around?
Update
I've attempted to add a
DROP statement as suggested here, but it still does not work:DECLARE @a bit = 1;
BEGIN
SELECT * INTO #aTemp FROM OPENROWSET( ... );
IF @a = 0
BEGIN
IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
BEGIN
DROP TABLE #bTemp;
END
SELECT ... INTO #bTemp FROM #aTemp;
END
ELSE
BEGIN
IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
BEGIN
DROP TABLE #bTemp;
END
SELECT ... INTO #bTemp FROM #aTemp;
END
ENDSolution
Per the documentation:
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
I ended up creating the table before the
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
I ended up creating the table before the
IF block like so: DECLARE @a bit = 1;
BEGIN
IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
BEGIN
DROP TABLE #bTemp;
END
CREATE TABLE #bTemp (
[c] int);
IF @a = 0
BEGIN
INSERT INTO #bTemp
SELECT 1 AS [c];
END
ELSE
BEGIN
INSERT INTO #bTemp
SELECT 1 AS [c];
END
DROP TABLE #bTemp;
ENDCode Snippets
DECLARE @a bit = 1;
BEGIN
IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
BEGIN
DROP TABLE #bTemp;
END
CREATE TABLE #bTemp (
[c] int);
IF @a = 0
BEGIN
INSERT INTO #bTemp
SELECT 1 AS [c];
END
ELSE
BEGIN
INSERT INTO #bTemp
SELECT 1 AS [c];
END
DROP TABLE #bTemp;
ENDContext
StackExchange Database Administrators Q#49628, answer score: 4
Revisions (0)
No revisions yet.