Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: writin a Stored procedure

    hi
    i think that the problem that i mentioned in pre post, can solved if such a value exist(xx).
    suppose that a value exist(xx), when u put it in this statement, sql consider all the values for that rows(similar case: that line dosnt exist)

    Code:
     
    CREATE PROCEDURE sp_test
    @hei int =xx
    @wei int =xx
    AS
    SELECT * FROM TBUsers WHERE 
    Age>30 
    AND 
    Height=@hei
    AND 
    Weight=@wei
    GO
    something that mean "all" for sql.
    anybody know about being or not being of this value?
    Last edited by bono56; 07-06-04 at 08:51.

  2. #2
    Join Date
    Mar 2004
    Posts
    80
    <code>
    CREATE PROCEDURE sp_test
    @hei int =null,
    @wei int =null
    AS
    SELECT * FROM TBUsers WHERE
    Age>30
    AND
    (Height=@hei or @hei is null)
    AND
    (Weight=@wei or @wei is null)

    GO
    </code>

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by theguru
    <code>
    CREATE PROCEDURE sp_test
    @hei int =null,
    @wei int =null
    AS
    SELECT * FROM TBUsers WHERE
    Age>30
    AND
    (Height=@hei or @hei is null)
    AND
    (Weight=@wei or @wei is null)

    GO
    </code>
    You probably meant this:

    ...AND Height=isnull(@hei, Height)
    AND Weight = isnull(@wei, Weight)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Mar 2004
    Posts
    80
    yup this is much simpler.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    You probably meant this:

    ...AND Height=isnull(@hei, Height)
    AND Weight = isnull(@wei, Weight)
    Doesn't that degenerate into a table scan? I generally prefer using dynamic SQL for this kind of problem... While it is rather messy, it avoids a lot of performance issues.

    -PatP

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How is this a scan?

    Code:
    USE Northwind
    GO
    
    CREATE INDEX Orders_ShipRegion ON Orders(ShipRegion)
    GO
    
    sp_Help Orders
    GO
    
    --[CTRL]+K
    
    SELECT OrderId, ShipRegion FROM Orders
    WHERE ShipRegion = 'RJ' OR ShipRegion IS NULL 
    GO
    
    DROP INDEX Orders.Orders_ShipRegion
    GO
    And ISNULL(...

    will scan....
    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.

  7. #7
    Join Date
    May 2004
    Posts
    144
    hi
    can u explain more about ur method pat?
    TanX

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh boy, not dyanmic, Pat, please. Besides, ISNULL would yield index scan.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by bono56
    hi
    can u explain more about ur method pat?
    TanX

    Well...I have seen dynamic blow away compiled sql....

    But usually because of a poor up front design...

    OK, Pat's method...

    Get about 10 feet of rope....tie one end to a big rock, and the other around your waist...

    stand on top of a picturesque so. cal cliff overlooking the pacific, throw said bolder off the cliff...

    Kinda like

    Code:
    USE Northwind
    GO
    
    DECLARE @sql varchar(8000)
    SET @sql = 'SELECT * FROM Orders'
    EXEC(@sql)
    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.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Dynamic blowing...well, I've seen a 6.5 dynamic completing in 1 hour and 45 minutes, while after upgrading (no matter how) to 2K never finishing after 48 hours...Go figure...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    Dynamic blowing...

    Now THERE's a concept.....
    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.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I used to be hard-core anti-dynamic too, until I came across a hairy dynamic procedure that handled filtering for a dozen different optional parameters. The dynamic SQL only checked for parameters that were actually supplied. I told the client that the procedure would run faster if I converted it to direct SQL, and I ended up with egg on my face when the dang thing ran several times SLOWER as direct SQL. I think I tried both methods for handling the optional parameters (OR, and ISNULL()), and I ended up going back to dynamic.

    Lesson learned. Even dynamic sql has a usefull purpose sometimes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Now that's not the heads down, blinders on, total path destruction kinda guy I know...

    What happened?

    Found religon?

    Or are you drunk?

    Tell me you're not going soft....
    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.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, if he says cursors have their place, then we will know it is the armageddon ;-).



    And before the flamethrowers come out, yes, I have allowed cursors on very small, very infrequent, batch operations.

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How about this for cursors (assuming that we're talking about a situation when for every row of a subset we need to fire a stored procedure):

    - Create a stored procedure from the script and replace cursor with straight select that would produce a batch containing the execution of another stored procedure against each row in the original result set;
    - Programmatically create a scheduled task that would:
    - execute the above procedure and send output to a .SQL file;
    - execute the .SQL file created in the first step.
    - Execute the scheduled task.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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