Refactor T sql code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Refactor T sql code

Author

Topic

hrishy
Starting Member

47 Posts

Posted - 2008-10-20 : 14:40:31

Hi

I have inherited some legacy code and reading the code it looks like its more of copy paste something like



if some_condition
begin
select col1,
       col2
from table t1
inner join t2
on t1.col1=t2.col2
else 
if some_other_condition
select col1,
       col2
from table t1
inner join t2
on t1.col1=t2.col2
where col1=5
else
if some_other_condition
select col1,
       col2
from table t1
inner join t2
on t1.col1=t2.col2
where col2=5
..

is there way i can refactor this code and no copy paste the same sql 10 times but write it once and keep on appending the conditions for each of the case ?

does my query make sense

regards
Hrishy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 14:44:52

[code]select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
where (col1=5 and condition2)
or (col2=5 and condition3)
or condition1[/code]

hrishy
Starting Member

47 Posts

Posted - 2008-10-20 : 15:58:10

Hi

Thanks for your reply.
But the condition is something that may not be matched with the join something like


if day='Monday'
begin
select col1,
       col2
from table t1
inner join t2
on t1.col1=t2.col2
else 
if day='Tuesday'
select col1,
       col2
from table t1
inner join t2
on t1.col1=t2.col2
where col1=5
else
if day='Wednesday'
select col1,
       col2
from table t1
inner join t2
on t1.col1=t2.col2
where col2=5

not the if conditions may not be present in the table

regards
hrishy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-20 : 16:07:19

Do you have tried Visa's solution?
In the WHERE-Clause you can use conditions no matter when they're not present in the table.
For example:
select col1 from table1 where 1=1 and 'yak' = 'yak'

Webfred

Planning replaces chance by mistake

hrishy
Starting Member

47 Posts

Posted - 2008-10-20 : 16:11:39

Hi

I am sorry i am not sure to use this condition in a where clause


select col1,
       col2
from table t1
inner join t2
on t1.col1=t2.col2
where col1=5
and day='Monday'   --I think sql wouldnt compile here

regards
hrishy

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-20 : 16:17:32

If you are talking about current day you are correct. But you can use

DATEPART(dw, GETDATE())
Will return a numeric value for the Day of the Week that you can use.
from BOL

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-20 : 16:19:28

do you have a column called 'day' ? Or are you trying to check if the current day is Monday if so. Instead of

and day='Monday'

try

and datename(dw, getdate())='Monday'

hrishy
Starting Member

47 Posts

Posted - 2008-10-20 : 16:20:47

Hi

Thanks again for looking into this.
I used day as just an example it can be anything

if Country='USA'
begin
select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
else
if Country='Canada'
select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
where col1=5
else
if Country='Sweden'
select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
where col2=5
.....

[/code]

regards
Hrishy

hrishy
Starting Member

47 Posts

Posted - 2008-10-20 : 16:21:32

Hi

No i dont have a column called day

regards
Hrishy

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-20 : 16:26:02

Then try posting your actual code so we can see what you are trying to do and maybe we can come up with a way to do it.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-20 : 16:27:02

quote:
Originally posted by hrishy

Hi

I am sorry i am not sure to use this condition in a where clause

select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
where col1=5
and day='Monday' --I think sql wouldnt compile here

regards
hrishy

Hello hrishy,
coming from here:

quote:
if some_condition
begin
select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
else ...

There was no reason to think "some_condition" looks like anything that is a problem for sql because what you have posted was SQL-Code wasn't it?

Planning replaces chance by mistake

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-20 : 16:34:16

[code]select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
where
where
1 = case when datename(dw, getdate()) = 'Monday' then 1 else
case when datename(dw, getdate())= 'Tuesday' and col1 = 5 then 1 else
case when datename(dw, getdate())= 'Wednesday' and col2 = 5 then 1 else 0 end end end
[/code]

quote:
Originally posted by hrishy

Hi

Thanks for your reply.
But the condition is something that may not be matched with the join something like

[code]
if day='Monday'
begin
select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
else
if day='Tuesday'
select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
where col1=5
else
if day='Wednesday'
select col1,
col2
from table t1
inner join t2
on t1.col1=t2.col2
where col2=5

[/code]

not the if conditions may not be present in the table

regards
hrishy

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-20 : 16:38:48

quote:
Originally posted by jhocutt

Then try posting your actual code so we can see what you are trying to do and maybe we can come up with a way to do it.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

agree, we are getting paid $50/hr to solve your problems you know...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-20 : 16:43:30

Oh!
Then I have to economize...

Planning replaces chance by mistake

hrishy
Starting Member

47 Posts

Posted - 2008-10-20 : 17:16:19

Hi

Ohh no please i appreciate your help very much.
Its just that when i get paid at the end of the month i need to give my salarys worth to my company thats why i wanted to refactor the code.

I will post the code tomorrow after i simplify and obfuscate the code

regards
Hrishy

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-20 : 17:23:34

If you can simplify it then you dont need us.
Is there something in this code that can not be posted?
Like hard coded SSNs or something?

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

hrishy
Starting Member

47 Posts

Posted - 2008-10-20 : 17:26:46

Hi

Well if i simplify it and obfuscate the names then it will be easier for you guys to help me i think.

I am not in office now so dont have acesses to source.

regards
Hrishy

hrishy
Starting Member

47 Posts

Posted - 2008-10-22 : 04:24:44

Hi

Here is a very simple t-sql that needs to be refactored.


If [@empid]='ALL'
--Select all employees
select empid
from employees
else
--if only one particular employee needs to be selected
select empid
from employees
where empid=@empid

There are other variants of this like


If [@empid]='ALL'
--Select all employees
select empid,ename,bankaccount
from employees
else
--if only one particular employee needs to be selected
select empid,ename,dob,ssn
from employees
where empid=@empid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 04:31:32

it can be written simply like this

select empid,ename,dob,ssn
from employees
where empid=@empid or @empid='ALL'

hrishy
Starting Member

47 Posts

Posted - 2008-10-23 : 04:01:06

Hi

Vishak thanks for your reply
Is there a way to write something like this


select empid,ename,dob,ssn
from employees
where empid=CASE WHEN @empid='ALL'
                 THEN '%'
                 ELSE @empid

But i dont know what to write in the part where there THEN '%'

regards
Hrishy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 04:14:03

why use %? what was problem with soln i gave?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources