September 27, 2011 at 12:11 pm
Here is the piece of code that is causing the issue:
declare @VP_ATTUID varchar(20)
declare @id int
declare @rowNum int
declare @maxrows int
CREATE TABLE #TempRevenue_GM (ATTUID VARCHAR(10), TITLE VARCHAR(10), VP_ATTUID VARCHAR(10), ATTAINMENT DECIMAL (20,2), Rank INT)
IF OBJECT_ID('tempdb..#SRRANKS_VP') IS NOT NULL DROP TABLE #SRRANKS_VP
select ATTUID, Title, VP_ATTUID, [POS REV ATTAIN], IDENTITY(int,1,1) as RANK into #SRRANKS_VP from dbo.tbl_CDE_Daily where 1=0 order by title
insert into #SRRANKS_VP select ATTUID, Title, VP_ATTUID, [POS REV ATTAIN] from tbl_CDE_Daily where title = 'VP' order by title, [POS REV ATTAIN] DESC
select top 1 @id = RANK, @VP_ATTUID = VP_ATTUID from #SRRANKS_VP
select @maxRows = count(*) from #SRRANKS_VP
set @rowNum = 0
-- this will until the last row is reached
WHILE @rowNum < @maxRows
BEGIN
set @rowNum = @rowNum + 1
-- this is where you can now do something like Pass the VP_ATTUID to the ranking
-- process, for now, we will just print the VP_ATTUID to the output screen
print ('Rank this VP! ' + @VP_ATTUID)
SELECT ATTUID, Title, VP_ATTUID
,[POS REV ATTAIN] as Attainment
INTO #tempSR --drop table #tempSR
FROM dbo.tbl_CDE_DAILY
WHERE Title = 'GM'
AND VP_ATTUID = @VP_ATTUID
select ATTUID, Title, VP_ATTUID, ATTAINMENT, IDENTITY(int,1,1) as RANK into #SRRANKS from #tempSR where 1=0 --drop table #RANKS
insert into #SRRANKS select ATTUID, Title, VP_ATTUID, ATTAINMENT from #tempSR order by attainment DESC
Select ATTUID, Title, VP_ATTUID,t2.Attainment, z.ranking as RANK
into #NETRevenue
from
(select min(t1.rank) as Ranking,t1.attainment
from #SRranks t1 group by t1.attainment) z
join #SRranks t2 on z.attainment = t2.attainment
and t2.Attainment <> 0
ORDER BY VP_ATTUID, RANK
--SELECT * FROM #TEMPSR ORDER BY ATTUID
insert into #TempRevenue_GM
SELECT * FROM #NETREVENUE
ORDER BY RANK
-- now we grab the next row making sure the ID of the next row
-- is greater than previous row
select top 1 @id = RANK, @VP_ATTUID = VP_ATTUID from #SRRANKS_VP where RANK > @id
IF OBJECT_ID('tempdb..#TEMPSR') IS NOT NULL DROP TABLE #TEMPSR
IF OBJECT_ID('tempdb..#SRRANKS') IS NOT NULL DROP TABLE #SRRANKS
IF OBJECT_ID('tempdb..#NETRevenue') IS NOT NULL DROP TABLE #NETRevenue
END
-- declare @VP_ATTUID varchar(20)
-- declare @id int
-- declare @rowNum int
-- declare @maxrows int
CREATE TABLE #TempRevenue_CM (ATTUID VARCHAR(10), TITLE VARCHAR(10), VP_ATTUID VARCHAR(10), ATTAINMENT DECIMAL (20,2), Rank INT)
IF OBJECT_ID('tempdb..#SRRANKS_VP') IS NOT NULL DROP TABLE #SRRANKS_VP
select ATTUID, Title, VP_ATTUID, [POS REV ATTAIN], IDENTITY(int,1,1) as RANK into #SRRANKS_VP from dbo.tbl_CDE_Daily where 1=0 order by title
insert into #SRRANKS_VP select ATTUID, Title, VP_ATTUID, [POS REV ATTAIN] from tbl_CDE_Daily where title = 'VP' order by title, [POS REV ATTAIN] DESC
select top 1 @id = RANK, @VP_ATTUID = VP_ATTUID from #SRRANKS_VP
select @maxRows = count(*) from #SRRANKS_VP
set @rowNum = 0
-- this will until the last row is reached
WHILE @rowNum < @maxRows
BEGIN
set @rowNum = @rowNum + 1
-- this is where you can now do something like Pass the VP_ATTUID to the ranking
-- process, for now, we will just print the VP_ATTUID to the output screen
print ('Rank this VP! ' + @VP_ATTUID)
SELECT ATTUID, Title, VP_ATTUID
,[POS REV ATTAIN] as Attainment
INTO #tempSR --drop table #tempSR
FROM dbo.tbl_CDE_DAILY
WHERE Title = 'CM'
AND VP_ATTUID = @VP_ATTUID
select ATTUID, Title, VP_ATTUID, ATTAINMENT, IDENTITY(int,1,1) as RANK into #SRRANKS from #tempSR where 1=0 --drop table #RANKS
insert into #SRRANKS select ATTUID, Title, VP_ATTUID, ATTAINMENT from #tempSR order by attainment DESC
Select ATTUID, Title, VP_ATTUID,t2.Attainment, z.ranking as RANK
into #NETRevenue
from
(select min(t1.rank) as Ranking,t1.attainment
from #SRranks t1 group by t1.attainment) z
join #SRranks t2 on z.attainment = t2.attainment
and t2.Attainment <> 0
ORDER BY VP_ATTUID, RANK
--SELECT * FROM #TEMPSR ORDER BY ATTUID
insert into #TempRevenue_CM
SELECT * FROM #NETREVENUE
ORDER BY RANK
-- now we grab the next row making sure the ID of the next row
-- is greater than previous row
select top 1 @id = RANK, @VP_ATTUID = VP_ATTUID from #SRRANKS_VP where RANK > @id
IF OBJECT_ID('tempdb..#TEMPSR') IS NOT NULL DROP TABLE #TEMPSR
IF OBJECT_ID('tempdb..#SRRANKS') IS NOT NULL DROP TABLE #SRRANKS
IF OBJECT_ID('tempdb..#NETRevenue') IS NOT NULL DROP TABLE #NETRevenue
END
drop table #TEMPSR
drop table #SRRANKS
drop table #NETRevenue
drop table #SRRANKS_VP
When I run this code I get the following error:
Server: Msg 2714, Level 16, State 1, Line 53
There is already an object named '#SRRANKS_VP' in the database.
Server: Msg 2714, Level 16, State 1, Line 68
There is already an object named '#tempSR' in the database.
Server: Msg 2714, Level 16, State 1, Line 71
There is already an object named '#SRRANKS' in the database.
Server: Msg 2714, Level 16, State 1, Line 75
There is already an object named '#NETRevenue' in the database.
Server: Msg 170, Level 15, State 1, Line 77
Line 77: Incorrect syntax near 'z'.
I put drop table in and I still get the error. Can anyone please help! :crying:
September 27, 2011 at 12:16 pm
Drop the tables manually, then run it. The query is stopping before it gets to the end (and therefore never actually dropping the tables).
September 27, 2011 at 12:22 pm
I thought the drop table command was the manual way to drop the tables...is there another way?
September 27, 2011 at 12:22 pm
In the procedure, add below syntax to drop temp tables if existing...
IF OBJECT_ID('tempdb..#TempTableName') IS NOT NULL
DROP TABLE #TempTableName
After this statement, put in the create temp table statement.
Thanks,
Vasu
September 27, 2011 at 12:25 pm
ss5523 (9/27/2011)
I thought the drop table command was the manual way to drop the tables...is there another way?
I mean run the drop commands, and then run that code. Also, adding vasu's conditional drops at the very beginning will also help, but keep your drops in at the bottom.
September 27, 2011 at 12:35 pm
I tried that but got the same error...utimately I want to run a loop query 4 times with different varaibles in a store procedure but will not allow me because it will not drop the temp tables being used within the loops. When I run this outside of a store procedure and put "GO" between each loop it works beautifully, however, store procedures do not recongize "GO" because it is a terminator.
September 27, 2011 at 12:43 pm
Try this
DECLARE @VP_ATTUID VARCHAR(20)
DECLARE @id INT
DECLARE @rowNum INT
DECLARE @maxrows INT
IF Object_id('tempdb..#TempRevenue_GM') IS NOT NULL
DROP TABLE #TempRevenue_GM
IF Object_id('tempdb..#TempRevenue_CM') IS NOT NULL
DROP TABLE #TempRevenue_GM
IF Object_id('tempdb..#SRRANKS_VP') IS NOT NULL
DROP TABLE #SRRANKS_VP
IF Object_id('tempdb..#tempSR') IS NOT NULL
DROP TABLE #tempSR
IF Object_id('tempdb..#SRRANKS') IS NOT NULL
DROP TABLE #SRRANKS
IF Object_id('tempdb..#NETRevenue') IS NOT NULL
DROP TABLE #NETRevenue
CREATE TABLE #TempRevenue_GM
(
ATTUID VARCHAR(10),
TITLE VARCHAR(10),
VP_ATTUID VARCHAR(10),
ATTAINMENT DECIMAL(20, 2),
Rank INT
)
SELECT ATTUID,
Title,
VP_ATTUID,
[POS REV ATTAIN],
IDENTITY(INT, 1, 1) AS RANK
INTO #SRRANKS_VP
FROM dbo.tbl_CDE_Daily
WHERE 1 = 0
ORDER BY title
INSERT INTO #SRRANKS_VP
SELECT ATTUID,
Title,
VP_ATTUID,
[POS REV ATTAIN]
FROM tbl_CDE_Daily
WHERE title = 'VP'
ORDER BY title,
[POS REV ATTAIN] DESC
SELECT TOP 1 @id = RANK,
@VP_ATTUID = VP_ATTUID
FROM #SRRANKS_VP
SELECT @maxRows = Count(*)
FROM #SRRANKS_VP
SET @rowNum = 0
-- this will until the last row is reached
WHILE @rowNum < @maxRows
BEGIN
SET @rowNum = @rowNum + 1
-- this is where you can now do something like Pass the VP_ATTUID to the ranking
-- process, for now, we will just print the VP_ATTUID to the output screen
PRINT ( 'Rank this VP! ' + @VP_ATTUID )
SELECT ATTUID,
Title,
VP_ATTUID,
[POS REV ATTAIN] AS Attainment
INTO #tempSR --drop table #tempSR
FROM dbo.tbl_CDE_DAILY
WHERE Title = 'GM'
AND VP_ATTUID = @VP_ATTUID
SELECT ATTUID,
Title,
VP_ATTUID,
ATTAINMENT,
IDENTITY(INT, 1, 1) AS RANK
INTO #SRRANKS
FROM #tempSR
WHERE 1 = 0 --drop table #RANKS
INSERT INTO #SRRANKS
SELECT ATTUID,
Title,
VP_ATTUID,
ATTAINMENT
FROM #tempSR
ORDER BY attainment DESC
SELECT ATTUID,
Title,
VP_ATTUID,
t2.Attainment,
z.ranking AS RANK
INTO #NETRevenue
FROM (SELECT Min(t1.rank) AS Ranking,
t1.attainment
FROM #SRranks t1
GROUP BY t1.attainment) z
JOIN #SRranks t2
ON z.attainment = t2.attainment
AND t2.Attainment <> 0
ORDER BY VP_ATTUID,
RANK
--SELECT * FROM #TEMPSR ORDER BY ATTUID
INSERT INTO #TempRevenue_GM
SELECT *
FROM #NETREVENUE
ORDER BY RANK
-- now we grab the next row making sure the ID of the next row
-- is greater than previous row
SELECT TOP 1 @id = RANK,
@VP_ATTUID = VP_ATTUID
FROM #SRRANKS_VP
WHERE RANK > @id
END
-- declare @VP_ATTUID varchar(20)
-- declare @id int
-- declare @rowNum int
-- declare @maxrows int
CREATE TABLE #TempRevenue_CM
(
ATTUID VARCHAR(10),
TITLE VARCHAR(10),
VP_ATTUID VARCHAR(10),
ATTAINMENT DECIMAL(20, 2),
Rank INT
)
SELECT ATTUID,
Title,
VP_ATTUID,
[POS REV ATTAIN],
IDENTITY(INT, 1, 1) AS RANK
INTO #SRRANKS_VP
FROM dbo.tbl_CDE_Daily
WHERE 1 = 0
ORDER BY title
INSERT INTO #SRRANKS_VP
SELECT ATTUID,
Title,
VP_ATTUID,
[POS REV ATTAIN]
FROM tbl_CDE_Daily
WHERE title = 'VP'
ORDER BY title,
[POS REV ATTAIN] DESC
SELECT TOP 1 @id = RANK,
@VP_ATTUID = VP_ATTUID
FROM #SRRANKS_VP
SELECT @maxRows = Count(*)
FROM #SRRANKS_VP
SET @rowNum = 0
-- this will until the last row is reached
WHILE @rowNum < @maxRows
BEGIN
SET @rowNum = @rowNum + 1
-- this is where you can now do something like Pass the VP_ATTUID to the ranking
-- process, for now, we will just print the VP_ATTUID to the output screen
PRINT ( 'Rank this VP! ' + @VP_ATTUID )
SELECT ATTUID,
Title,
VP_ATTUID,
[POS REV ATTAIN] AS Attainment
INTO #tempSR --drop table #tempSR
FROM dbo.tbl_CDE_DAILY
WHERE Title = 'CM'
AND VP_ATTUID = @VP_ATTUID
SELECT ATTUID,
Title,
VP_ATTUID,
ATTAINMENT,
IDENTITY(INT, 1, 1) AS RANK
INTO #SRRANKS
FROM #tempSR
WHERE 1 = 0 --drop table #RANKS
INSERT INTO #SRRANKS
SELECT ATTUID,
Title,
VP_ATTUID,
ATTAINMENT
FROM #tempSR
ORDER BY attainment DESC
SELECT ATTUID,
Title,
VP_ATTUID,
t2.Attainment,
z.ranking AS RANK
INTO #NETRevenue
FROM (SELECT Min(t1.rank) AS Ranking,
t1.attainment
FROM #SRranks t1
GROUP BY t1.attainment) z
JOIN #SRranks t2
ON z.attainment = t2.attainment
AND t2.Attainment <> 0
ORDER BY VP_ATTUID,
RANK
--SELECT * FROM #TEMPSR ORDER BY ATTUID
INSERT INTO #TempRevenue_CM
SELECT *
FROM #NETREVENUE
ORDER BY RANK
-- now we grab the next row making sure the ID of the next row
-- is greater than previous row
SELECT TOP 1 @id = RANK,
@VP_ATTUID = VP_ATTUID
FROM #SRRANKS_VP
WHERE RANK > @id
END
IF Object_id('tempdb..#TempRevenue_GM') IS NOT NULL
DROP TABLE #TempRevenue_GM
IF Object_id('tempdb..#TempRevenue_CM') IS NOT NULL
DROP TABLE #TempRevenue_GM
IF Object_id('tempdb..#SRRANKS_VP') IS NOT NULL
DROP TABLE #SRRANKS_VP
IF Object_id('tempdb..#tempSR') IS NOT NULL
DROP TABLE #tempSR
IF Object_id('tempdb..#SRRANKS') IS NOT NULL
DROP TABLE #SRRANKS
IF Object_id('tempdb..#NETRevenue') IS NOT NULL
DROP TABLE #NETRevenue
GO
September 27, 2011 at 1:11 pm
Thank you but I got the following error:
Server: Msg 2714, Level 16, State 1, Line 146
There is already an object named '#SRRANKS_VP' in the database.
Server: Msg 2714, Level 16, State 1, Line 183
There is already an object named '#tempSR' in the database.
Server: Msg 2714, Level 16, State 1, Line 193
There is already an object named '#SRRANKS' in the database.
Server: Msg 2714, Level 16, State 1, Line 209
There is already an object named '#NETRevenue' in the database.
Server: Msg 134, Level 15, State 1, Line 127
The variable name '@VP_ATTUID' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 134, Level 15, State 1, Line 128
The variable name '@id' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 134, Level 15, State 1, Line 129
The variable name '@rowNum' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 134, Level 15, State 1, Line 131
The variable name '@maxrows' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 170, Level 15, State 1, Line 212
Line 212: Incorrect syntax near 'z'.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply