I am trying to write a cursor. I have created a table for capturing login name and time from sys.dm_exec_sessions. Now, I need to write a cursor to update the login time to last login time if the login already exists and insert a row in case login does not exists in my table. I have come up with following, but unfortunately I am getting an error with subquery returning more than one value for it. Any ideas???
declare @log as varchar(200)
declare @log_time as datetime
declare LoginsSize cursor for
SELECT login_name, login_time
FROM sys.dm_exec_sessions
open LoginsSize
fetch next from LoginsSize into @log, @log_time
while( @@fetch_status = 0)
begin
If (Select Login from [dbo].[LoginsForDBUserList]) = @log
Begin
UPDATE [dbo].[LoginsForDBUserList]
SET LastLoginTime = @log_time
WHERE [login]= @log
END
Else
Begin
Insert Into [dbo].[LoginsForDBUserList]
SELECT login_name, login_time
FROM sys.dm_exec_sessions
END
fetch next from LoginsSize into @log, @log_time
close LoginsSize
deallocate LoginsSize
end
Best Answer
I think that error is being generated because of this line
Multiple rows are being returned from the left side of the equal sign and is attempting to be compared to the single value
@log
.I've made some adjustments to your original query and it works for me. Give this a try: