more than one query in CASE-WHEN-THEN statement

  • if normal CASE-WHEN-THEN statement, i can do this,

    USE pubs

    GO

    SELECT 'Price Category' =

    CASE

    WHEN price IS NULL THEN 'Not yet priced'

    WHEN price < 10 THEN (Select column1 from table 1)

    WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'

    ELSE 'Expensive book!'

    END,

    CAST(title AS varchar(20)) AS 'Shortened Title'

    FROM titles

    ORDER BY price

    GO

    but what if I have more than one query in CASE-WHEN-THEN statement?

    I found I cannot do these: "BEGIN Select ... END" or "goto label AAA":

    USE pubs

    GO

    SELECT 'Price Category' =

    CASE

    WHEN price IS NULL THEN 'Not yet priced'

    WHEN price < 10 THEN (goto label AAA)

    WHEN price >= 10 and price < 20 THEN (BEGIN Select ... END)

    ELSE 'Expensive book!'

    END,

    CAST(title AS varchar(20)) AS 'Shortened Title'

    FROM titles

    ORDER BY price

    GO

  • Well, you have to keep in mind, that you are inside of a select... so you can hardly jump somewhere else (GOTO). You can do that between two SELECTs, but not while you are halfway through one (and not only that, you also would be jumping out of the CASE statement before it was ended properly). And the BEGIN..END block has similar limitations.

    Could you please explain, why exactly you need GOTO and BEGIN..END inside CASE? Is that just a theoretical question, or do you have some practical example where you think you need it?

  • The original SP is something like this and need to be fine-tuned:

    if @FieldName='var1'

    select @FValue = Col1 from Table1

    else if @FieldName = 'var2'

    select @FValue = col1 from Table2

    else if @FieldName = 'var3'

    select @FValue = col1 from Table3

    else if @FieldName = 'var4'

    select @FValue = col1 from Table4

    ...

    else if @FieldName = 'var5'

    begin -

    if @bIsToTester = 1

    begin

    exec @ret = dbo.uspXXX ...

    insert @BBB ( ...)

    select ...

  • Well, now this is very different from the original post... the original post contained GOTO inside of a SELECT (that is, between SELECT and FROM, actually you were trying to jump somewhere else while calculating the value for resultset). Here, it is a set of IF..THEN constructs, which is perfectly OK. So where in this code would be the need to use something like you posted before - GOTO label?

  • :DHi there,

    I agree with vladan, I was also a bit confused on the two examples you gave... But don't worry, I think I found what you need... Use a SCALAR Function which returns only one value.

    Heres an example, I hope I got it right...

    And I hope it helps...

    CREATE TABLE #TABLE1

    (

    IDINT IDENTITY,

    ItemVARCHAR(MAX)

    )

    CREATE TABLE TABLE2

    (

    IDINT IDENTITY,

    ItemVARCHAR(MAX)

    )

    INSERT INTO #TABLE1 VALUES('A')

    INSERT INTO #TABLE1 VALUES('B')

    INSERT INTO #TABLE1 VALUES('C')

    INSERT INTO TABLE2 VALUES('D')

    INSERT INTO TABLE2 VALUES('E')

    INSERT INTO TABLE2 VALUES('F')

    GO

    CREATE FUNCTION dbo.Table2Value()

    RETURNS VARCHAR(MAX)

    AS

    BEGIN

    DECLARE @Val2 VARCHAR(MAX)

    SELECT @Val2=(SELECT TOP 1 Item FROM TABLE2)

    SET @VAL2=@VAL2+'(o^_'')'

    RETURN @Val2

    END

    GO

    CREATE TABLE #TABLE3

    (

    IDINT IDENTITY,

    ValINT

    )

    INSERT INTO #TABLE3 VALUES(5)

    INSERT INTO #TABLE3 VALUES(15)

    INSERT INTO #TABLE3 VALUES(25)

    SELECT * FROM #TABLE1

    SELECT * FROM TABLE2

    SELECT * FROM #TABLE3

    DECLARE @val INT

    SET @val=25

    SELECT NewItem = CASE

    WHEN Val<10 THEN (SELECT TOP 1 Item FROM #TABLE1)

    WHEN Val>=10 and VAL<20 THEN dbo.Table2Value()

    ELSE 'test'

    END

    FROM #TABLE3

    GO

    DROP TABLE #TABLE1

    DROP TABLE TABLE2

    DROP TABLE #TABLE3

    DROP FUNCTION dbo.Table2Value

    Please tell me if this post was helpful ^__^ Thanks

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • I intend to do something like this:

    SELECT @FValue =

    CASE @FieldName

    WHEN 'var1'THEN (select Col1 from Table1)

    WHEN 'var2'THEN (select Col2 from Table1)

    WHEN 'var3'THEN (select Col3 from Table1)

    WHEN 'var4'THEN (select Col4 from Table1)

    ...

    WHEN 'var5'THEN (BEGIN

    if @bIsToTester = 1

    ... END)

    END

    HI, Quatrei.X, thanks for suggesting UDF, but what if I have output paratemer need to be captured in 'var5'?

  • anyone?

  • it looks like you are just trying to make a single procedure return a variety of different values...sort of a do-all proc.

    I would suggest getting rid of the case statment and just build a dynamic SQL

    If @Fieldname = 'Col1'

    BEGIN

    SET @SQL = @SQL + ',COL1 FROM SOMETABLE

    END

    If @Fieldname = 'Col2'

    BEGIN

    SET @SQL = @SQL + ',COL2FROM SOMETABLE

    END

    PRINT @SQL

    EXEC @SQL

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I intend to do something like this:

    SELECT @FValue =

    CASE @FieldName

    WHEN 'var1' THEN (select Col1 from Table1)

    WHEN 'var2' THEN (select Col2 from Table1)

    WHEN 'var3' THEN (select Col3 from Table1)

    WHEN 'var4' THEN (select Col4 from Table1)

    ...

    WHEN 'var5' THEN (BEGIN

    if @bIsToTester = 1

    ... END)

    END

    An alternative solution is to place Table1 in the FROM clause and then use a CASE.

    SELECT @FValue =

    CASE @FieldName

    WHEN 'var1' THEN Table1.Col1

    WHEN 'var2' THEN Table1.Col2

    WHEN 'var3' THEN Table1.Col3

    WHEN 'var4' THEN Table1.Col4

    ELSE '?' -- do something intelligent ?

    END

    FROM ?

    ? LEFT OUTER JOIN? Table1

    on Table1.somecolumn? = ?.someothercolumn?

    As far as this statement:

    WHEN 'var5' THEN (BEGIN

    if @bIsToTester = 1

    ... END)

    More details are needed to provide a solution.

    SQL = Scarcely Qualifies as a Language

  • hi Lowell, Carl Federl, thanks for your suggestion. Althought the actual case is like similar to what Lowell think of ( and could be only choice), but using dynamic queries will against the purpose of my post, since I am trying to improve the performance of the query, maybe I need to analyze the execution plan more for other work-around. Thanks anyway.

  • What do you get when you do the following, Gan?

    [font="Courier New"]DECLARE @SomeNumber INT

    SELECT @SomeNumber = Number

    FROM Master.dbo.spt_Values

    SELECT @SomeNumber[/font]

    How many rows were processed? If you said "one", then you're very wrong and that's a possible source of your performance problem. You must correlated your sub-queries the way you have it. Better choice would be to properly join all the tables on properly indexed keys to maximize performance.

    By the way, the first SELECT above processes 2346 and uses the last row of the non-clustered index that was used by the execution plan as the final value.

    If you're processing as many or more rows to return just one value for each correlated sub-query, that would constitute a major performance problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I get 1469283328 for that. Sorry I do not quite understand what you are trying to explain. Can elaborate more? 😀

  • :DHi there again,

    Sorry GAN, I'm always offline during Sturdays and Sundays...

    About the output parameter you where talking about... My exmaple already has one, except that its not actually an output parameter but a return value. Have you tried exectuing my code and checked the sample output?

    oh yeah, by the way, heres a note, there should only be 1 return value, since the select statement looks for only one value...

    uhmmm... did I get it right? just reply if you need some modifications

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • Hi there,

    hey, wait a minute, are you using this to get values of NEW table column"S" or a SINGLE variable?

    cause some of one code says its for table columns and another say that its for a variable

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • Hi Quatrei.X, thanks for your reply.

    Sorry, maybe i should mention earlier. Actually inside the 'var5', it could be more that one output values returned. It makes "CASE" statement nearly impossible, right?

    if @FieldName='var1'

    select @FValue = Col1 from Table1

    else if @FieldName = 'var2'

    select @FValue = col1 from Table2

    else if @FieldName = 'var3'

    select @FValue = col1 from Table3

    else if @FieldName = 'var4'

    select @FValue = col1 from Table4

    ...

    else if @FieldName = 'var5'

    begin -

    if @bIsToTester = 1

    begin

    exec @ret = dbo.uspXXX ...

    insert @BBB ( ...)

    select ...

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply