Multiple If's - 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)

Multiple If's

Author

Topic

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-08 : 09:10:57

Can anyone suggest improvements on this

If @X = 1
Begin
Exec Sp_proc1
End
If @X =2
Begin
Exec Sp_Proc2
End
If @X = 3
begin
Exec Sp_Proc3
end
If @X = 4
Begin
Exec Sp_proc4
End
If @x = 5
Begin
Exec Sp_proc6
end

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 09:16:50

This is equivalent, but less code:

If @X = 1 Exec Sp_proc1
Else If @X = 2 Exec Sp_Proc2
Else If @X = 3 Exec Sp_Proc3
Else If @X = 4 Exec Sp_proc4
Else If @x = 5 Exec Sp_proc6

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-08 : 09:19:09

If @X = 1
Exec Sp_proc1
else if @X = 2
Exec Sp_proc2
....so on

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-08 : 09:20:49

Not effecient way

EXEC('EXEC Sp_Proc'+cast(@X as varchar(2)))

Madhivanan

Failing to plan is Planning to fail

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-08 : 09:27:35

hey madhivanan, can you giv ur suggestion in detail plz

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 10:05:50

I would recommend avoiding dynamic SQL. In fact, I would recommend NEVER using dynamic SQL. There are VERY FEW times that is is ACTUALLY appropriate. The reason I recommend against it is because the use of dynamic SQL opens vulnerabilities in your system. SQL Injection attacks use this very type of situation. He is casting @X as a varchar(2) which leaves little room for an attack, but I want you to be VERY CAREFUL WHEN EVEN THINKING ABOUT DYNAMIC SQL.

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-08 : 10:16:16

thnks for the suggestion. Will consider it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-09 : 01:20:03

and read more on dynamic sql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources