SQL Server Performance

@@Rowcount

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Aug 25, 2009.

  1. atulgoswami New Member

    Assume the case below

    *******************************************************************************************************************************************************************************
    Declare @rcount int
    Select @rcount=count(colID) from table1 where colname = 'XYZ'
    If @rcount=0 then
    BEGIN
    Select * from table1
    END
    Else
    BEGIN
    Select * from table1 where colname = 'XYZ'
    END
    *******************************************************************************************************************************************************************************

    Now SQL 2005 has @@RowCount, which Returns the number of rows affected by the last statement.

    By this, Would it be a good approach to write like this?

    *******************************************************************************************************************************************************************************
    Declare @rcount int

    Select * from table1 where colname = 'XYZ'

    Select @rcount = @@RowCount

    If @rcount = 0 Then
    BEGIN
    Select * from table1
    END
    *******************************************************************************************************************************************************************************

    Thanks
    atulgoswami, Aug 25, 2009
    #2

  2. FrankKalis Moderator

    The second approach may give you 2 resultsets back. If you don't want this, or your client can't handle it, you should stick with the first approach.
    Btw, @@ROWCOUNT has been around forever. [:)]
    FrankKalis, Aug 25, 2009
    #3

  3. atulgoswami New Member

    <P mce_keep="true">[quote user="FrankKalis"] <P>The second approach may give you 2 resultsets back. If you don't want this, or your client can't handle it, you should stick with the first approach.</P><P>Btw, @@ROWCOUNT has been around forever. <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif" mce_src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"> <BR></P><P>[/quote]</P><P>Thanks and i think you are right in getting stick to first approach. I am writing stored procedures only and not sure about whether client would be able to handle double recordset or not.<BR></P><P>However i was just thinking to avoid count operation. Is there any other way to achieve this?</P><P>Thanks</P>
    atulgoswami, Aug 25, 2009
    #4

  4. FrankKalis Moderator

    Sure, just use the EXISTS() clause.
    FrankKalis, Aug 25, 2009
    #5

  5. Dugi New Member

    Hi Frank!
    Can you give us a sample, plz, using the EXIST() in this case!?
    Thank you
    Dugi
    Dugi, Aug 26, 2009
    #6

  6. FrankKalis Moderator

    Welcome to the forum!
    IF EXISTS (SELECT 1
    FROM
    table1
    WHERE
    colname = 'XYZ')
    BEGIN
    Select * from table1 where colname = 'XYZ';
    END
    ELSE
    BEGIN
    Select * from table1;
    END
    FrankKalis, Aug 26, 2009
    #7

  7. FrankKalis Moderator

    ...or
    IF NOT EXISTS (SELECT 1
    FROM
    table1
    WHERE
    colname = 'XYZ')
    BEGIN
    Select * from table1;
    END
    ELSE
    BEGIN
    Select * from table1 where colname = 'XYZ';
    END
    Whatever is more likely to be the case.
    FrankKalis, Aug 26, 2009
    #8

  8. Dugi New Member

    Thank you Frank for fast reply!
    Regards,
    Dugi
    Dugi, Aug 26, 2009
    #9

Share This Page