SP not working. Help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP not working. Help needed

Author

Topic

savvy95
Starting Member

23 Posts

Posted - 2006-02-09 : 08:57:56

This doesn't work:
Use AD_Test2
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'ADChangeDate' AND type = 'P')
DROP PROCEDURE ADChangeDate
GO
Create Procedure ADChangeDate
@Object varchar(50),
@Days tinyint
As
select a.[name], a.editlast, a.whenchanged, a.@Object, g.@Object as 'USERS'
from auditlog a
join USERS g
on a.objectguid = g.objectguid
where a.@Object <> g.@Object
and exists (select dn from auditlog)
and a.insertdate > getdate() - @Days
order by a.editlast desc

The error is:
Server: Msg 170, Level 15, State 1, Procedure ADChangeDate, Line 5
Line 5: Incorrect syntax near '@Object'.
Server: Msg 156, Level 15, State 1, Procedure ADChangeDate, Line 11
Incorrect syntax near the keyword 'and'.

Can't I use a parameter in the SELECT statement?

Can anybody help me?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 09:04:58

quote:
Can't I use a parameter in the SELECT statement?

Yes. But not table variable or column variable

If you really need to do this, then use dynamic sql exec().

declare @sql varchar(1000)
select @sql = 'select a.[name], a.editlast, a.whenchanged, a.' + @Object + ', g.' + @Object + 'as [USERS]' + char(13)
select @sql = @sql + 'from  auditlog a' + char(13)
select @sql = @sql + 'join  USERS g' + char(13)
select @sql = @sql + 'on    a.objectguid = g.objectguid' + char(13)
select @sql = @sql + 'where a.' + @Object + ' <> g.' + @Object + char(13)
select @sql = @sql + 'and   exists (select dn from auditlog)' + char(13)
select @sql = @sql + 'and   a.insertdate > getdate() - ' + convert(varchar(10), @Days) + char(13)
select @sql = @sql + 'order by a.editlast desc'
exec (@sql)

----------------------------------
'KH'
everything that has a beginning has an end

savvy95
Starting Member

23 Posts

Posted - 2006-02-09 : 09:29:52

I works.....I knew somebody would know the answer...
What do you mean though by "If you really need to do this"..
Is something inherintly wrong with it?
I'm just curious as the db is small and I'm the only one accessing presently.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 09:31:51

maybe i should ask "Why are you doing this ?"

----------------------------------
'KH'
everything that has a beginning has an end

savvy95
Starting Member

23 Posts

Posted - 2006-02-09 : 09:49:30

As there is no record of changes made in AD, I've developed a way of tracking them.

I want to compare the differences between the USERS and AUDITLOG Tables on various @Objects (Objects from AD - displayName, sAMAccountName, mail, etc)

The end result is to retain a record of what changes occurs in AD and be able to display them.

I have tables in the Db:
Users
Computers
Groups
AuditLog

So i've been able to retrieve the objects from AD and put the differences in the AUDITLOG and the objects in their respective tables. Now I've been working on a way to display those differences.

Thanks to you I've been able to do it.

Hope this helps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources