Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Question Unanswered: Optional WHERE in stored procedures

    Hello guys,
    I want to implement a searching with a stored procedure. As an example I have a table like
    > tableA ; Id,Name,Surname,Gender(bit) 0 -Male , 1 - Female

    If i want to choose all records that are male, I could simply have a query like this :
    SELECT * FROM tableA WHERE Gender = 0
    and vice versa for female.
    If i want to choose all rows in the table i should omit where clause.
    The problem is , when i use a stored procedure i should send a parameter. And i dont know if there is a possible way to to do this. When i try such procedure :
    CREATE astoredprc
    (@gender bit)
    AS
    SELECT * FROM tableA WHERE Gender = @Gender
    GO
    --
    In such prc. i can only get rows with a where clause. What if i want to use the same procedure with all rows and omiting where. I have many fields like this and I m unsure if I should use a text query or not ...
    Thanks for all of u from now on

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The second option id more effecient

    Code:
    CREATE astoredprc
    (@gender bit = NULL)
    AS
    SELECT * FROM tableA WHERE Gender = ISNULL(@Gender,Gender)
    GO
    
    
    Or
    
    CREATE astoredprc
    (@gender bit = NULL)
    AS
    
    IF @gender ISNULL 
        SELECT * FROM tableA
      ELSE
        SELECT * FROM tableA WHERE Gender = @Gender
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2003
    Posts
    7
    Yes but if there is many parameters it is not good. I ve more than 10 tables and 33 parameters.
    The first option worked well so far. But i become curious now if there is any cons of this technique. Because u told that first is more efficent

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...turn on the show execution plan and you'll see a scan...

    You got a sample of the query/sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2003
    Posts
    7
    I am currently coding the sproc. I didnt implement yet the other parameters.

    "turn on the show execution plan and you'll see a scan..." Sorry but I didnt get what you mean here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •