Post reply

@@ROWCOUNT Query

  • July 16, 2007 at 2:22 pm

    #372856

    I have the following query that uses @@ROWCOUNT

    SELECT

    TOP 1 SampleUnit_ID FROM #Sps

    IF

    @@ROWCOUNT = 0

    BEGIN

    -- Update #Log

    INSERT INTO #Log (RecordType, RecordsValue)

    Select 'No matching records were sampled for an HCAHPS unit.', LTRIM(STR(@@ROWCOUNT))

     

    END

    Unfortunately it is returning a data set when the entire SP is ran which I don't want

    Is there a way to run something like

    IF @@ROWCOUNT = SELECT TOP 1 sampleunit_id from #SPs

    ...

     

    Thanks

  • July 16, 2007 at 2:39 pm

    #719801

    Try

    IF NOT EXISTS ( SELECT 1 FROM #Sps)

    BEGIN

    ....

    END

     

  • July 16, 2007 at 2:56 pm

    #719809

    @@ROWCOUNT is highly volatile. Once referenced, it is reset.

    IF @@ROWCOUNT = 0  -- Checked and reset.

    INSERT ... VALUES (@@ROWCOUNT)

     

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

  • July 16, 2007 at 3:11 pm

    #719813

    So what do you suggest?

    I need the Select Top 10 ... line to set the @@ROWCOUNT because it check the temp table to see if it has a record in it. But it returns a recordset which I don't want.

  • July 16, 2007 at 3:28 pm

    #719818

    Suggestion is already posted.

    _____________
    Code for TallyGenerator

  • July 16, 2007 at 5:52 pm

    #719834

    DECLARE @MyRows INT 

    SELECT TOP 1 SampleUnit_ID FROM #Sps

    SET @MyRows = @@ROWCOUNT

    IF @MyRows @@ROWCOUNT = 0

    BEGIN

    -- Update #Log

    INSERT INTO #Log (RecordType, RecordsValue)

    Select 'No matching records were sampled for an HCAHPS unit.', LTRIM(STR(@MyRows @@ROWCOUNT))

    END

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

  • July 17, 2007 at 7:17 am

    #720014

    This worked like a champ. Thanks

  • July 17, 2007 at 7:24 am

    #720017

    You bet... thanks for the feedback.

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

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

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