Create View Declar Variable question

Question

Create View Declar Variable question

lsalih

SSCertifiable

Points: 6143
More actions
March 12, 2019 at 6:43 am

#371664

I have below query which I need to create a view for it, can you please guide me to what is the best way to create the view? Thank you.
create table #ListOfUsers
(
       [FULLNAME] varchar(200),
       varchar(100),
       [ACCOUNT_NAME] varchar(200),
       [ACCOUNT_NAME_WITH_DOMAIN] varchar(200),
       [DEPARTMENT] varchar(200),
       [TITLE] varchar(200)
       ,[AD_GROUP] varchar(200)
)
SET NOCOUNT ON
declare @GCN varchar(100),@DISTGName varchar(1000), @USERINFO varchar (4000), @RESULT varchar (4000);
declare gC cursor
for
select cn, distinguishedName
from openquery
(ADSI,'SELECT cn, distinguishedName
FROM ''LDAP://admin.schools.lcps/DC=Admin,DC=schools,DC=lcps''
WHERE objectClass = ''Group''
AND NAME=''DEVELOPERS''
or NAME=''DBAS''
or NAME=''SYSTEMS''
')
open gC
FETCH NEXT FROM gC INTO @GCN, @DISTGName
WHILE @@FETCH_STATUS = 0
BEGIN
set @USERINFO = '''select title, department, sAMAccountName, mail , displayname
FROM ''''LDAP://admin.schools.lcps/DC=Admin,DC=schools,DC=lcps''''
WHERE objectCategory = ''''Person'''' AND objectClass = ''''user''''
AND memberOf=''''' + @DISTGName + '''''';
set @RESULT = 'select '+ ''''+@GCN+'''' +' As AD_GROUP, title, department, sAMAccountName, ''ADMIN\''+sAmAccountName , mail , displayname from openquery(ADSI,'+ @USERINFO +''') '
insert into #ListOfUsers([AD_GROUP], [TITLE], [DEPARTMENT],[ACCOUNT_NAME], [ACCOUNT_NAME_WITH_DOMAIN], , [FULLNAME])
EXEC (@RESULT)
--print @RESULT
FETCH NEXT FROM gC INTO @GCN, @DISTGName
END
CLOSE gC
DEALLOCATE gC
select * from #ListOfUsers

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76944 More actions · Answer 1

A view can only consist of a single SELECT statement. You cannot create tables, declare, open, close, deallocate, or fetch from cursors, execute dynamic sql, or declare variables. You may want to make this a stored procedure instead.

In any case, you may want to look at getting rid of the CURSOR.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

lsalih SSCertifiable Points: 6143 More actions · Answer 2

You are correct, I know you can't declare variables in a view, I guess I should have said how to make this as a function or a SP and create a view based on that.
To get the result I needed, I created a job to insert the data into a table. So I am good.
As for using the cursor, can you please tell me why I need to get rid of it? I am getting the LDAP group names and looping through to get a list of users for each AD group. If you have a better way of doing it, please feel free to share an example. Thank you.