I am trying to a simple if funtion to determine whether a condition is met. The code is attached. when i try to run it for myself (which should execute the bottom half) it is telling me
"There is already an object named '#security' in the database."
but if I select from #security I get:
Invalid object name '#security'
-- If user has access to everything
IF ((select count(dept_num) from dbo.DS_LIST_USER_ACCESS where dept_num = '%'
and rtrim(suser_sname()) = rtrim(userid)) <> 0)
select distinct gl_prefix,zc_dep_rpt_grp_6.rpt_grp_six
inner join zc_dep_rpt_grp_6 on clarity_dep.rpt_grp_six = zc_dep_rpt_grp_6.rpt_grp_six
where zc_dep_rpt_grp_6.name not like 'do no%'
-- If user has limited access
-- First select the departments where the division is %
-- Bounce the department up against clarity dep to get the divisions
select distinct rpt_grp_six as dept,gl_prefix AS Division
inner join dbo.DS_LIST_USER_ACCESS on dept_num = rpt_grp_six
where division = '%' and rtrim(suser_sname()) = rtrim(userid)
-- Next get all the departments where the division is not a wildcard
-- and put them in the same security table.
Insert into #security
select distinct dept_num,division
where division <> '%'
and rtrim(suser_sname()) = rtrim(userid)
select * from #security