Post reply

Better way to write?

  • October 5, 2007 at 1:13 pm

    #198778

    Greetings,

    So the stupid question of the moment is how can I make this work without doing the select statement the way I am?

    SET @bLookForChangedCUSIPs = 0

    SELECTCOUNT(*) ChangedCUSIPs

    FROMPerformance.dbo.gChangedCUSIPs

    WHERECUSIPKey = @vchVAGK+@vchPAC

    IF @@ROWCOUNT>0 SET @bLookForChangedCUSIPs = 1

    Ultimately I am just trying to set a flag. Of course, the problem is that I get a returned row from the select statement which while it doesn't do anything to the application it is in just looks like crud. I have alot of these little flag tests so I dont really want to use a procedure with OUTPUT.

    Any thoughts?

    thanks,

    Chris

  • October 5, 2007 at 1:45 pm

    #739704

    change this:

    SET @bLookForChangedCUSIPs = 0

    SELECTCOUNT(*) ChangedCUSIPs

    FROMPerformance.dbo.gChangedCUSIPs

    WHERECUSIPKey = @vchVAGK+@vchPAC

    IF @@ROWCOUNT>0 SET @bLookForChangedCUSIPs = 1

    to this:

    set @bLookForChangedCUSIPs

    select @bLookForChangedCUSIPs = 1 from Performance.dbo.gChangedCUSIPs WHERE CUSIPKey = @vchVAGK+@vchPAC

    😎

  • October 5, 2007 at 2:34 pm

    #739726

    cool, thanks 🙂

  • October 6, 2007 at 3:07 pm

    #739819

    Or, just...

    SELECT @bLookForChangedCUSIPs = SIGN(COUNT(*))

    FROM Performance.dbo.gChangedCUSIPs

    WHERE CUSIPKey = @vchVAGK + @vchPAC

    --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)

  • October 8, 2007 at 2:49 am

    #739923

    COUNT needs to scan the whole table.

    Try this!

    IF EXISTS (SELECT * FROM Performance.dbo.gChangedCUSIPs WHERE CUSIPKey = @vchVAGK + @vchPAC)

    SET @bLookForChangedCUSIPs = 1

    ELSE

    SET @bLookForChangedCUSIPs = 0

    N 56°04'39.16"
    E 12°55'05.25"

  • October 8, 2007 at 5:18 pm

    #740192

    Even better!

    --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)

  • October 8, 2007 at 5:19 pm

    #740193

    Nice one, Peter.

  • October 9, 2007 at 12:20 am

    #740245

    Thank you!

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 8 posts - 1 through 7 (of 7 total)

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