Wesley.Groleau (7/27/2013)
For example, if the ROW (condition) then select col_A as @vrbl_X, col_B as @vrbl_Y
else, select L, M, N, O, P, Q, ...V and arrange them into several lines of a flat file according to X12 rules, inserting @vrbl_X, @vrbl_Y, and other globals at the appropriate places, and updating the globals conditionally as you go along.
Sure, a stored procedure CAN do it, but a script component or other CLR is easier. Especially when the DB is read-only and you can't stick a sproc into it!
I guess my question would be, why do you think that couldn't be done in a set-based or column-based fashion? Remember that "Set-Based" doesn't mean "all in one query". And just because a database is "Read-Only" doesn't mean you can't have a stored proc that runs against it.
So far as "a script component or other CLR is easier" goes, that depends on a whole lot but mostly depends on the knowledge of the programmer both about T-SQL and the "other" language and the actual problem itself, whatever it is.. Are they smarter on the declarative or procedural side of the house? I agree that I've seen some folks that have worked absolute wonders using CLR (especially SQLCLR) as it was meant to be used. I've also seen some people do some absolutely stupid things with it just because they weren't as familiar with T-SQL as they were with typical front-end-style programming.
Of course, that's not just limited to T-SQL. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.