I like that technique as well. Once in an interview with very defensive and antagonistic interviewers (they didn’t think I was worth the rate I quoted), they asked me how I’d select a comma separated list of names from a users table. I knew they were looking for a cursor solution. I wrote the one you presented and they gleefully told me I was wrong.
I didn’t get the gig, but did write their manager an email stating that they’re interview process was flawed and that my solution was 1000 times faster than theirs.
Yeah, I was a little punk.
Very cool Haacked. Well, not cool that the interviewers didn’t get it, but that you stood your ground. I think its pretty clear that would NOT have been happy there anyway.
The saying…."In the land of the blind, the one-eyed man is King” applies to your experience. So maybe a little rewording…"In the land SQL wannabe interviewer, the CURSOR approach is King!". Anyway, you inspired me to share a similar experience that I think many of us have encountered about the root causes of project success and failure. I’ll be blogging it soon. Thanks!
Comment from: Vern Rabe
I, too, use that technique often. MS recommends using INFORMATION_SCHEMA views instead of system tables whenever possible. The revised script using INFORMATION_SCHEMA views:
DECLARE @c varchar(4000), @t sysname
SET @c = ‘’
SELECT @c = @c + C.COLUMN_NAME + ‘, ‘
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE c.TABLE_NAME = @t
ORDER BY ORDINAL_POSITION
SELECT Substring(@c, 1, Datalength(@c) - 2)
Thanks Vern. Being an “old timer” I forget about the information_schema views.
Three years since it was posted, but I found it today and it was a big help. Nice hack … thanks.
Comment from: Siva
good keep up your SQL skills
Comment from: Gayathri
Thanks Vern and Wayne,
I found this very useful for my code.
This post has 4127 feedbacks awaiting moderation...