There is already an object Msg 2714, Level 16, State 6, Line 15

Hi

I'm getting the following error

There is already an object  Msg 2714, Level 16, State 6, Line 15

There is already an object named '#LTIP_IDS' in the database


 
declare
	@p_maturity_date datetime,
	@p_ltip_ids varchar(1000),
	@p_user varchar(20),
	@rpt_type int

set @p_maturity_date = '2007-12-09'
set @p_ltip_ids = null
set @p_user = 'prodaa3'
set @rpt_type = 1
set @p_ltip_ids =[dbo].ufnParamOrNull(@p_ltip_ids)

create table #LTIP_IDS(LTIP_ID int)
insert #LTIP_IDS(LTIP_ID)	select convert(int,value)	from [dbo].fn_csvToLookupTable(@p_ltip_ids, default)

declare @award_year int, @cycle int
select @award_year= award_year, @cycle=cycle from [dbo].[fn_getYearCycle](@p_maturity_date)

--select PC_ID, BG_DESC, LOB_NAME, PC_NAME, BA_DESC
--into #PERMISSIONS 
--from [dbo].USER_BGS where AWARD_YEAR = @award_year	and CYCLE = @cycle	and  user_name = @p_user		


select 
	sa.EMPLOYEE_PROFILE_ID,
	ltip.CHILD_plan_short_name PLAN_SHORT_NAME, 
	replace(ltip.CHILD_plan_short_name, 'TU','')+ case when ltip.CHILD_plan_short_name like '%TU%' then 'TU' else '' end as PLAN_GROUP,
	ltip.ISSUE_DATE, 
	ltip.VAL_DATE, 
	ltip.EXPIRY_DATE,
	ltip.GRANT_PRICE,
	isNUll(DEFERRED_UNITS,
		(ltc.TOTAL_UNITS -ltc.FORFEITED_UNITS) * sa.DSU_DEFER_PCT / 100)
		 as matShareUnitsDeferred,

	sa.DSU_DEFER_PCT,
	sa.US_TAXPAYER,
	sa.CONFIRM_DT,
	sa.GROUPING,

	29 PLAN_CODE, 1 FUND_NO, case  when sa.US_TAXPAYER=1 then 4 else 2 end as ACCT_NO,
	PLAN_CODE as SU_PLAN_CODE,
	FUND_NO as SU_FUND_NO,
	ACCT_NO as SU_ACCT_NO
into #AWARDS
from   [dbo].[SU_MATURITY_SOR_VIEW] sa
	inner join [dbo].LONG_TERM_COMPENSATION ltc on sa.LTIC_ID = ltc.LTIC_ID	
	inner join [dbo].[LONG_TERM_INCENTIVE_PLAN_HIERARCHY_VIEW] ltip on ltip.LTIP_ID = ltc.LTIP_ID
	inner join [dbo].ACS_LTIP_MAP alm on ltip.LTIP_ID = alm.LTIP_ID 
	INNER JOIN #LTIP_IDS as tli ON (tli.LTIP_ID = (Case When @p_ltip_ids is null Then tli.LTIP_ID Else ltip.LTIP_ID End))
	left join [dbo].DEFERRAL_ELECTION de on ltc.LTIC_ID = de.LTIC_ID
where ltip.CHILD_plan_short_name not like 'VSU%' and  ltip.CHILD_plan_short_name not like 'OPT%'
	and sa.VAL_DATE = @p_maturity_date
	and ltc.TOTAL_UNITS -ltc.FORFEITED_UNITS>0




SELECT 
	a.EMPLOYEE_PROFILE_ID,
	eiv.B_AREA_DESC,
	eiv.EXEC_DESC,
	eiv.RL,
	eiv.MICRO,
	eiv.EMPLOYEE_ID,
	eiv.LAST_NAME,
	eiv.GIVEN_NAMES,
	eiv.POSITION,
	eiv.DIVISION,
	eiv.EP_COUNTRY_DESC,
	eiv.POS_CRNCY_SMBL,

	a.US_TAXPAYER,
	a.CONFIRM_DT,
	a.GROUPING,
	PLAN_SHORT_NAME, 
	PLAN_GROUP,
	ISSUE_DATE, 
	VAL_DATE,		
	DSU_DEFER_PCT,
	matShareUnitsDeferred,
	PLAN_CODE, 
	FUND_NO, 
	ACCT_NO,

	'000005533' AS CLIENT_NO, 
	RIGHT(REPLICATE('0',7) + eiv.EMPLOYEE_ID,7) + REPLICATE(' ',13) AS PARTICIPANT_ID, 
	RIGHT(REPLICATE('0', 5) + CONVERT(varchar(2), a.PLAN_CODE), 5) AS VPLAN_NO, 
	RIGHT(REPLICATE('0', 2) + CONVERT(varchar(1), a.ACCT_NO), 2) AS VACCT_NO, 
	RIGHT(REPLICATE('0', 2) + CONVERT(varchar(1), a.FUND_NO), 2) AS VFUND_NO, 
	CONVERT(varchar(8), a.ISSUE_DATE, 112) AS GRANT_DATE, 
	CONVERT(varchar(8), a.EXPIRY_DATE, 112) AS EXPIRY_DATE, 
	'01' AS VESTING_NO, 
	CONVERT(varchar(13),REPLICATE('0', 13 - LEN(REPLACE(matShareUnitsDeferred, '.', ''))) + REPLACE(matShareUnitsDeferred, '.', '')) AS DEFERRED_UNITS,
	CONVERT(varchar(9),REPLICATE('0', 9 - LEN(REPLACE(a.GRANT_PRICE, '.', ''))) + REPLACE(a.GRANT_PRICE, '.', '')) AS GRANT_PRICE, 
	CONVERT(varchar(9),REPLICATE('0', 9 - LEN(REPLACE(a.GRANT_PRICE, '.', ''))) + REPLACE(a.GRANT_PRICE, '.', '')) AS FMV_PRICE
	,
	RIGHT(REPLICATE('0', 5) + CONVERT(varchar(2), a.SU_PLAN_CODE), 5) AS SU_PLAN_CODE, 
	RIGHT(REPLICATE('0', 2) + CONVERT(varchar(1), a.SU_FUND_NO), 2) AS SU_FUND_NO, 
	RIGHT(REPLICATE('0', 2) + CONVERT(varchar(1), a.SU_ACCT_NO), 2) AS SU_ACCT_NO 

into #recordset
FROM
	#AWARDS a 
inner JOIN  EMPLOYEE_INFO_VIEW eiv on eiv.EMPLOYEE_PROFILE_ID = a.EMPLOYEE_PROFILE_ID and AWARD_YEAR=@award_year and CYCLE=@cycle
order by LAST_NAME,PLAN_SHORT_NAME


if @rpt_type = 1 -- vendor
select CLIENT_NO, PARTICIPANT_ID,VPLAN_NO,VACCT_NO, VFUND_NO,GRANT_DATE,EXPIRY_DATE
	, CONVERT(varchar(13),REPLICATE('0', 13 - LEN(REPLACE(sum(matShareUnitsDeferred), '.', ''))) + REPLACE(sum(matShareUnitsDeferred), '.', '')) as DEFERRED_UNITS
	, sum(matShareUnitsDeferred) matShareUnitsDeferred,
	SU_PLAN_CODE, 
	SU_FUND_NO, 
	SU_ACCT_NO
from #recordset
group by CLIENT_NO, PARTICIPANT_ID,VPLAN_NO,VACCT_NO, VFUND_NO,GRANT_DATE,EXPIRY_DATE,
	SU_PLAN_CODE, 
	SU_FUND_NO, 
	SU_ACCT_NO

else 
	select * from #recordset

	
drop table  #LTIP_IDS,  #AWARDS,  #recordset

Open in new window

FAH_Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
To conditionally delete that table if it exists..
IF OBJECT_ID('tempdb..#LTIP_IDS') IS NOT NULL
   DROP TABLE #LTIP_IDS

-- Your code goes here. 

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
above the statement run
drop table '#LTIP_IDS
drop table #awards
drop table #recordset


if your query encountered errors it would never reach the bottom drop tables which is why you're getting that error.

you could always do a check to see if it exists as well before dropping it.
0
 
FAH_Author Commented:
now same issue is coming for #awards
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
You have to do it for all tables temporary tables.  (Any table starting with a #)
0
 
David ToddSenior DBACommented:
Hi

For me good coding says to always check for temp table existence and drop it prior to creating it or selecting into.

I achieved some performance benefit on long etl procedure, but searching for the table from end of file, and after last reference of table doing an if table exists drop ...

The performance benefit was slightly smaller memory footprint for the procedure when it was running.

HTH
  David
0
 
Vignesh SCommented:
Write the drop table query at the end of the stored procedure.
First run the drop temporary tables query("Drop Table Table_Name") separately (select appropriate query and press F5).
Then  Run the query from start to till the top of drop tmp table query("Drop Table Table_Name").
Now you won't get such an error
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.