Looking for the SQLCommand = VB Select Ca

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 2000 Forums

Transact-SQL (2000)

Looking for the SQLCommand = VB Select Ca

Author

Topic

leleux
Starting Member

3 Posts

Posted - 2004-03-15 : 16:12:37

I am trying to find the Sql command that is equivalent to the VB Command Select Case. I have 6 different transactions that take you down 6 different paths. Instead of using a bunch of if then tests, I would like to use a Select Case.

Thank you for your time

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-15 : 16:17:16

Have you looked up CASE in SQL Server Books Online? It can be used in a SELECT statement.

Tara

leleux
Starting Member

3 Posts

Posted - 2004-03-15 : 16:25:36

Yes I have. Case seems only to be used to set a variable equal to a value. Not execute a set of commands.

I need:
Select Case MsgState
Case "OK"
'continue
Case "ClientSSN"
MsgBox Msg
DoCmd.CancelEvent
[ClientSSN].SetFocus
Exit Sub
Case "PayeeDbID"
MsgBox Msg
DoCmd.CancelEvent
[PayeeDbID].SetFocus
Exit Sub
Case "AccountNo"
MsgBox Msg
DoCmd.CancelEvent
[AccountNo].SetFocus
Exit Sub

Am I reading the Online Books incorrectly?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-15 : 16:35:31

Could you explain what you are trying to do? Show us some sample data and what the expected result set should be.

Tara

leleux
Starting Member

3 Posts

Posted - 2004-03-15 : 16:42:22

Here is my samples. I am trying to execute different command depending upon what TID is equal to. I would like to get away from the if then structure as I have 6 comparisons to do.

Sample data

TID Name
1 Normal
2 Override
3 Fast

Current code reads:

if TID = 1
begin
exec sp_NormalType @ID
end

else if TID = 2
begin
exec sp_OverrideType @ID
end

else if TID = 3
begin
exec sp_FastType @ID
end

I hope this helps.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-15 : 16:45:50

Since you are executing a stored procedure, you'll need to use the IF statements. But perhaps you can do away with the stored procedures and put everything in the CASE. We would need to see the code for the stored procedures to determine this.

BTW, do not name any of your stored procedures with sp_. SQL Server checks to see if this object exists in the master database first before it moves to the current database. This is a performance hit.

Tara

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-15 : 17:22:14

If your sprocs are different enough, it sounds like you could do this part as a combined case dynamic sql creation.

basically to create the line to execute you could do something like

select @execline = case when TID = 1 then 'sp_normaltype' when TID = 2 then 'sp_overridetype' etc, etc, etc

then you can execute the appropriate sproc.

It would probably be easier to have a single sproc with different tasks within based on the 'TID' value.

Case in SQL is not really a control of flow function the way it is in VB. Its used to assign different values based on conditions. There is a small assortment of control of flow available in SQL including if then else.

Chris

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-15 : 17:29:38

But don't forget about the performance problems and security problems with dynamic sql.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources