Select Case Help - 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 2000 Forums

SQL Server Development (2000)

Select Case Help

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-26 : 17:45:32

Hi, every one,
I have following t-sql that I want to covnert to SELECT CASE, but couldn't get it to work. Do I need to use dynamic sql or stay with the ugly first approach?

Thanks!

-----Here are the working part----
--try to get ratio from tbl override first if nothing then go after default
SELECT @Ratio = fltValue from tblActiveOverride WHERE txtBuyerCode = @txtBuyerCode AND txtType = @txtDataType and dteDelete IS NULL
IF ISNULL(@Ratio, 0) = 0
BEGIN
IF @txtDataType = 'Sales'
BEGIN
SELECT @Ratio = SalesActivePct from tmpActivePercent WHERE txtBuyerCode = @txtBuyerCode
END
END
IF ISNULL(@Ratio, 0) = 0
BEGIN
IF @txtDataType = 'GM'
BEGIN
SELECT @Ratio = GMActivePct from tmpActivePercent WHERE txtBuyerCode = @txtBuyerCode
END
END
--4 more @txtDataType to go
--next is the more condense version but currently err out on THEN SELECT
SELECT @Ratio = fltValue from tblActiveOverride WHERE txtBuyerCode = @txtBuyerCode AND txtType = @txtDataType and dteDelete IS NULL

IF ISNULL(@Ratio, 0) = 0
BEGIN
SELECT @Ratio = CASE @txtDataType
WHEN 'Sales' THEN SELECT SalesActivePct from tmpActivePercent WHERE txtBuyerCode = @txtBuyerCode
WHEN 'GM' THEN SELECT GMActivePct from tmpActivePercent WHERE txtBuyerCode = @txtBuyerCode
WHEN '...'
END
END
I can make a set of sample data, but maybe somebody can point out the problem right away with it. Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 00:26:51

[code]IF ISNULL(@Ratio, 0) = 0
BEGIN
IF @txtDataType='Sales' THEN
SELECT @Ratio =SalesActivePct from tmpActivePercent WHERE txtBuyerCode = @txtBuyerCode
ELSE IF @txtDataType='GM'
SELECT @Ratio =GMActivePct from tmpActivePercent WHERE txtBuyerCode = @txtBuyerCode
ELSE IF ...
[/code]

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-27 : 08:33:57

Looks like you are really overcomplicating things by passing in a parameter that says what value you want returned. What advantage is there to this? Just return *all* of the columns in the stored procedure and let the front end or the calling stored procedure pick whatever it wants to display or use!

i.e., all you need is:

SELECT SalesActivePct, GMActivePct, ...
from tmpActivePercent
WHERE txtBuyerCode = @txtBuyerCode

That's it!

- Jeff
http://weblogs.sqlteam.com/JeffS

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-27 : 09:41:45

Thank you for both of you.

Madhivanan
Your solution works. You are right, I don't need a CASE. For some reason, I missed that.

Jeff,
You're right. I did make the overcomplicating call here. However, I am also the front-end programmer. I have to do it either here or out there.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-27 : 10:28:13

>>You're right. I did make the overcomplicating call here. However, I am also the front-end programmer. I have to do it either here or out there.

And it is much, much, much easier, shorter, faster, simpler and more direct to simply do it at the front-end. Keep your SQL calls short, quick, and efficient and let your front end worry about what columns to display or to use from the results. If you are writing the front end as well, all the more reason to NOT do it in SQL !!

- Jeff
http://weblogs.sqlteam.com/JeffS

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-06-28 : 14:51:49

quote:
Originally posted by jsmith8858
And it is much, much, much easier, shorter, faster, simpler and more direct to simply do it at the front-end. Keep your SQL calls short, quick, and efficient and let your front end worry about what columns to display or to use from the results. If you are writing the front end as well, all the more reason to NOT do it in SQL !!

Uh oh...

Jeff,

I usually appreciate your posts, and the articles on your blog. However, I have to disagree with you here.

If you can reduce the amount of data that's being sent from the server to the client in your SQL, you should. This reduces both network traffic, saving bandwidth, and the resource usage of your application. That's why SELECT * is a bad idea most of the time, unless you know for a fact that you're going to a) use every single column, and b) only get a few rows back.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-28 : 16:13:56

I am not saying use SELECT * and return all columns all the time. I am saying if you need to pick from 3 columns in a table to use or display at the front end, for a single row, then just return all 3 and let the front end decide. Your code is shorter, cleaner and ultimately more efficient since you don't have a whole mess of IF-ELSE or CASE expressions, it is just a quick, efficient SELECT statement.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources