Weird, and definitely not wonderful
-
if LEN(@accountnumber) >= 4
begin
select * from dbo.Departments where accountnumber = @accountnumber order by deptname;
end
else
begin
select * from dbo.departments where catid = @catid order by deptname;
endI want to NEVER work with the guy (and I know his name, BTW) who wrote this crap. Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project!
-
if LEN(@accountnumber) >= 4
begin
select * from dbo.Departments where accountnumber = @accountnumber order by deptname;
end
else
begin
select * from dbo.departments where catid = @catid order by deptname;
endI want to NEVER work with the guy (and I know his name, BTW) who wrote this crap. Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project!
select *
from dbo.Departments
where (accountnumber = @accountnumber and LEN(@accountnumber) >= 4)
or catid = @catid
order by deptnameBetter? :D I'm wondering what the problem is exactly... Except for the weird IF statement, use of * in production code and horrible formatting.
public class SanderRossel : Lazy<Person>
{
public void DoWork()
{
throw new NotSupportedException();
}
} -
if LEN(@accountnumber) >= 4
begin
select * from dbo.Departments where accountnumber = @accountnumber order by deptname;
end
else
begin
select * from dbo.departments where catid = @catid order by deptname;
endI want to NEVER work with the guy (and I know his name, BTW) who wrote this crap. Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project!
-
select *
from dbo.Departments
where (accountnumber = @accountnumber and LEN(@accountnumber) >= 4)
or catid = @catid
order by deptnameBetter? :D I'm wondering what the problem is exactly... Except for the weird IF statement, use of * in production code and horrible formatting.
public class SanderRossel : Lazy<Person>
{
public void DoWork()
{
throw new NotSupportedException();
}
}Sander Rossel wrote:
I'm wondering what the problem is exactly...
The problem is that he's looking up the department by either account number or category ID. This is just bad practice and made worse by the fact (which I forgot to mention) that the PROC's name is "GetClientDepartments" which implies the list is being filtered by clients, not my accounts or categories. There should be at least two PROC's, one called "GetDepartmentsByAccountNumber" and the other "GetDepartmentsByCategory". It's just plain laziness that he put the functionality for two very different things into one proc. Now get this -- on the client side, he's always populating the department and catid values! Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project!
-
don't open it[^] Hopefully not your new client/employer? :laugh: - Sebastian
manchanx wrote:
Hopefully not your new client/employer?
No, thankfully. Haven't heard from them yet, this is code I'm cleaning up before (hopefully) I come on board with the other client. Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project!
-
Sander Rossel wrote:
I'm wondering what the problem is exactly...
The problem is that he's looking up the department by either account number or category ID. This is just bad practice and made worse by the fact (which I forgot to mention) that the PROC's name is "GetClientDepartments" which implies the list is being filtered by clients, not my accounts or categories. There should be at least two PROC's, one called "GetDepartmentsByAccountNumber" and the other "GetDepartmentsByCategory". It's just plain laziness that he put the functionality for two very different things into one proc. Now get this -- on the client side, he's always populating the department and catid values! Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project!
Well in that case it's A BIT weird. I've worked with such procedures a lot. They usually were part of some bigger procedure. This, for example, could be part of getting a product, but depending on the department and available data and some customer setting and lots of other stuff you would ultimately get 1 out of maybe 10 possible outcomes. Anyway, if you worked where I worked (notice past tense!) this wouldn't bother you as much as it does :laugh:
public class SanderRossel : Lazy<Person>
{
public void DoWork()
{
throw new NotSupportedException();
}
} -
if LEN(@accountnumber) >= 4
begin
select * from dbo.Departments where accountnumber = @accountnumber order by deptname;
end
else
begin
select * from dbo.departments where catid = @catid order by deptname;
endI want to NEVER work with the guy (and I know his name, BTW) who wrote this crap. Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project!
Yeah, needs a
CASE. :rolleyes:IFhas no place in SQL. He must not have been trained in set-based thinking. -
if LEN(@accountnumber) >= 4
begin
select * from dbo.Departments where accountnumber = @accountnumber order by deptname;
end
else
begin
select * from dbo.departments where catid = @catid order by deptname;
endI want to NEVER work with the guy (and I know his name, BTW) who wrote this crap. Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project!
I'd like to propose a simple extra switch to test Sanders code, while still be able to switch back to the old one.
if (select COUNT(*) from tblSettings where 'test' = 'true') = 0
begin
if LEN(@accountnumber) >= 4
begin
select * from dbo.Departments where accountnumber = @accountnumber order by deptname;
end
else
begin
select * from dbo.departments where catid = @catid order by deptname;
end
end
else
begin
select *
from dbo.Departments
where (accountnumber = @accountnumber and LEN(@accountnumber) >= 4)
or catid = @catid
order by deptname
end;You should replace the "4" with a value that you get from a settings-table ofcourse. You don't want to recompile your sproc simply because the length of catids' change. Better yet, replace it with the MIN(LEN(accountnumber)).
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
I'd like to propose a simple extra switch to test Sanders code, while still be able to switch back to the old one.
if (select COUNT(*) from tblSettings where 'test' = 'true') = 0
begin
if LEN(@accountnumber) >= 4
begin
select * from dbo.Departments where accountnumber = @accountnumber order by deptname;
end
else
begin
select * from dbo.departments where catid = @catid order by deptname;
end
end
else
begin
select *
from dbo.Departments
where (accountnumber = @accountnumber and LEN(@accountnumber) >= 4)
or catid = @catid
order by deptname
end;You should replace the "4" with a value that you get from a settings-table ofcourse. You don't want to recompile your sproc simply because the length of catids' change. Better yet, replace it with the MIN(LEN(accountnumber)).
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Well, having one stored procedure for each possible search parameter would be a REAL horror, IMHO. You can get to the hundreds of procedures really fast. I would likely have more than one parameter in the sproc, and default values, so I could write:
EXEC GetClientDepartments @accountNumber = 'xxxxxxxx';
-- or...
EXEC GetClientDepartments @catId = 'xxxx';t-SQL code can get really, really messy if you don't pay attention. But, other than that, the code is not really an horror (t-SQL code quality is a lot lower than what you're expecting). My 2c.
