Most problems can be solved without resorting to a cursor or while loop

Question

Post reply

Most problems can be solved without resorting to a cursor or while loop

sgambale

SSCommitted

Points: 1709
More actions
March 18, 2010 at 8:47 am

#142074

I have read that most problems can be solved without resorting to a cursor or while loop.
How can I accomplish this?
I want to query sys.sql_logins
(select * from sys.sql_logins where is_disabled = 0 and
is_policy_checked = 1 and
is_expiration_checked = 1
order by name)
to retrieve each login name I am interested in, then use loginproperty to see if their password is about to expire so I can inform them:
declare @name varchar(100)
select loginproperty('@name', 'passwordLastSetTime')
-- I can figure how to add code to find when it will expire --
My inclination is to use a loop and read one row at a time from sys.sql_logins, then feed each nmae to the loginproperty command.
How can I avoid while loop or cursor?
I use sql server 2005.
Thanks,
Steve

Viewing 5 posts - 1 through 4 (of 4 total)

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

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

select LOGINPROPERTY(name,'passwordLastSetTime') from sys.sql_logins

Converting oxygen into carbon dioxide, since 1955.

GSquared SSC Guru Points: 260824 More actions · Answer 2

I'd keep at least the name in the dataset:

SELECT *, loginproperty(name, 'passwordLastSetTime')

FROM sys.sql_logins AS SL

WHERE is_policy_checked = 1

AND is_expiration_checked = 1

ORDER BY name;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

For the future, if you ever run into an instance where you have to execute a stored procedure multiple times, instead of calling a function, you might also consider dynamic SQL as a solution. Using your loginproperty example, it would look something like this. However, the generated SELECT statements would be replaced by EXEC statements for whatever stored procedure. (Note: the following code is quick and dirty and doesn't represent best practices for parameterized dynamic SQL.)

declare @sql nvarchar(max)

declare @results table (Name varchar(50) primary key, PwdLastSet sql_variant)

set @sql = ''

select @sql = @sql+ '

select '''+name+''', loginproperty('''+name+''',''passwordLastSetTime'')'

from sys.sql_logins

-- where (add whatever criteria you like)

print @sql -- just to see what it looks like

insert into @results

exec sp_executeSQL @sql

select * from @results

Just keep believing that there is a way to do it without a loop or cursor and you will get the hang of thinking in sets.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sgambale SSCommitted Points: 1709 More actions · Answer 4

All valuable and fast replies.

Thanks guys!

I've used dynamic sql many times in Oracle, but it helps to see a t-sql example.