SQL QRY

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)
	BEGIN
	
		select distinct gl_prefix,zc_dep_rpt_grp_6.rpt_grp_six
		INTO #security
		from clarity_dep
		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%'
	END
 
 
-- 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
		INTO #security
		from clarity_dep 
		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 
		from dbo.DS_LIST_USER_ACCESS
		where division <> '%'
		and rtrim(suser_sname()) = rtrim(userid)
	
 
 
 
select * from #security

Open in new window

gdspeareAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
create table #security (gl_prefix  varchar(10) , rpt_grp_six   varchar(10) )  --change the datatypes
-- 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)
      BEGIN
                  INSERT INTO #security
            select distinct gl_prefix,zc_dep_rpt_grp_6.rpt_grp_six
            from clarity_dep
            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%'
      END
 
 
-- If user has limited access
    -- First select the departments where the division is %
    -- Bounce the department up against clarity dep to get the divisions
      INSERT INTO #security
    select distinct rpt_grp_six as dept,gl_prefix AS Division
    from clarity_dep
    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
    from dbo.DS_LIST_USER_ACCESS
    where division <> '%'
    and rtrim(suser_sname()) = rtrim(userid)
     
 
 
 
select * from #security
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.