none
@@rowcount

    Question

  •  

    Hi ,
          I have to develop a stored procedure.A parameter is passed to the stored procedure.

    select type  from table1 where id=@id1   

    If the above query results have any records then the records are returned to the function that called this stored procedure.
    But if the select query does not have any records ,then I have to check select type from table2 where id=@id1.

    The conditions in the where clause is the same,but they are 2 different tables.

    i.e First I need to check if any records exists based on the condition in where clause in table1.If there are no records found in table1,then
    I need to check in table2.


    Code in stored procedure:

    select type from table1 where id=@id1

    if @@rowcount=0

    select type  from table2 where id=@id1.

    I am not sure if the above mentioned code is correct.Please guide me .

    Thanks,
    cp


    cp_25
    Wednesday, February 18, 2009 3:18 PM

Answers

  • Aaron,
    Would EXISTS be a better test than Count(*) > 0... and by better I just mean more efficient?


    George
    Wednesday, February 18, 2009 4:49 PM
  • How about this:

    IF (SELECT COUNT(*) FROM table1 WHERE id = @id1) > 0  
        SELECT type FROM table1 WHERE id = @id1  
    ELSE 
        SELECT type FROM table2 WHERE id = @id1 

    Aaron Alton | thehobt.blogspot.com
    Wednesday, February 18, 2009 4:20 PM
  • gvee said:

    Aaron,
    Would EXISTS be a better test than Count(*) > 0... and by better I just mean more efficient?


    George



    Yes, yes it would.  Good point.

    CP - you can change 
    IF (SELECT COUNT(*) FROM table1 WHERE id = @id1) > 0
    to
    IF EXISTS (SELECT * FROM table1 WHERE id = @id1)

    George is right - IF EXISTS "stops" when it hits a single matching record, whereas COUNT(*) will find all matching records.

    Thanks, George.

    Aaron Alton | thehobt.blogspot.com
    Wednesday, February 18, 2009 4:52 PM

All replies

  • Hi CP,

    Your code looks good.  Are you having any trouble with it?
    Aaron Alton | thehobt.blogspot.com
    Wednesday, February 18, 2009 3:21 PM
  • Hi,
           I  tested the code mentioned below:
      Code in stored procedure:

    select type from table1 where id=@id1

    if @@rowcount=0

    select type  from table2 where id=@id1.

    when there are no records found in table1,it displays an empty recordset from table1 and the records found from table2.
    How to prevent from returning an empty record from table1 ?

    Please help me.

    Thanks,
    cp


    cp_25
    Wednesday, February 18, 2009 4:15 PM
  • How about this:

    IF (SELECT COUNT(*) FROM table1 WHERE id = @id1) > 0  
        SELECT type FROM table1 WHERE id = @id1  
    ELSE 
        SELECT type FROM table2 WHERE id = @id1 

    Aaron Alton | thehobt.blogspot.com
    Wednesday, February 18, 2009 4:20 PM
  • Hi,

     It worked great .

    I tried 3 different options:

    1.Have a record in table1 and table2  where id=@id1

    Result:Returned record from table1.

    2.Have a record only in table2 where id=@id1

    Result:Returned record from table2

    3.Did not have any records in both table1 and table2 where id=@id1.
    Result:No records were returned.

    It worked great.Thank you very much for your assistance.

    I would like to know where to include the SET NOCOUNT ON and SET NO COUNT OFF in the code .Should I include SET NOCOUNT ON  before
    the if statement and SET NOCOUNT OFF after the else block.

    Thanks,
    cp


    cp_25
    Wednesday, February 18, 2009 4:47 PM
  • Aaron,
    Would EXISTS be a better test than Count(*) > 0... and by better I just mean more efficient?


    George
    Wednesday, February 18, 2009 4:49 PM
  • gvee said:

    Aaron,
    Would EXISTS be a better test than Count(*) > 0... and by better I just mean more efficient?


    George



    Yes, yes it would.  Good point.

    CP - you can change 
    IF (SELECT COUNT(*) FROM table1 WHERE id = @id1) > 0
    to
    IF EXISTS (SELECT * FROM table1 WHERE id = @id1)

    George is right - IF EXISTS "stops" when it hits a single matching record, whereas COUNT(*) will find all matching records.

    Thanks, George.

    Aaron Alton | thehobt.blogspot.com
    Wednesday, February 18, 2009 4:52 PM
  • Hi,
             Thanks for your fast response.

    You have asked me to change

     IF (SELECT COUNT(*) FROM table1 WHERE id = @id1) > 0
    to
    IF EXISTS (SELECT * FROM table1 WHERE id = @id1)

    Question1: what if there is always only one matching record for the condition specified?Will it make any difference between using EXISTS and COUNT(*).

    Question2:

    CREATE PROCEDURE [dbo].[Test] AS

     

    declare @id1 varchar(20)

    set @id1='1'

    set nocount on

     

    IF (SELECT COUNT(*) FROM table1 WHERE id = @id1) > 0
     

        select  type  from table1 where id=@id1

    else

        select  type  from table2 where id=@id1

     

    set nocount off

    GO


    Have I included the set nocount on and set nocount off in the right place in the stored procedure?I came to know that it improves the performance.
    Does it really make any difference if we don't include it in stored procedure?

    Thanks for your help.I really appreciate it.

    Thanks,
    cp

    cp_25
    Wednesday, February 18, 2009 6:20 PM
  • As a best practice you should SET NOCOUNT ON at the top of all stored procedures, and there is not need to set it OFF at the bottom because its existence is only scoped to the execution of the stored procedure.  SET Options revert back to connection defaults when they go out of scope, as in the end of the stored procedure.

    Your procedure code should look like:

    CREATE PROCEDURE [dbo].[Test] (@id1 varchar(20))  
    AS 
    SET NOCOUNT ON 
     
    IF EXISTS (SELECT 1 FROM table1 WHERE id = @id1)  
        BEGIN 
            SELECT type    
            FROM table1   
            WHERE id=@id1  
        END 
    ELSE 
        BEGIN 
            SELECT type    
            FROM table2   
            WHERE id=@id1  
        END 
     
    GO 

    You should use SELECT 1 with the EXISTS clause because it does not perform the metadata expansion that a * would to get all the columns then restrict it back to only what is necessary to satisfy the EXISTS.  Conor Cunningham put a little detail on this in the following post:

    http://www.sqlskills.com/BLOGS/CONOR/post/EXISTS-Subqueries-SELECT-1-vs-SELECT-*.aspx


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, February 18, 2009 7:15 PM
  • Aaron Alton said:
    Yes, yes it would.  Good point.


    It's only fair that I get a turn at spotting a mistake like this - you picked me up on a biggie earlier! :P

    George
    Wednesday, February 18, 2009 9:37 PM