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

    Unanswered: help Stored proc

    Hi,
    Could any one please help me in creating sp.
    It should accept one input parameter. when you pass value 1 to this parameter it should show all odd numbers from 1 to 100 and when you pass value 2 it should show all even numbers from 1 to 100.

    Thanks in advance.
    -ss

  2. #2
    Join Date
    Jul 2004
    Posts
    52
    Try this:

    create procedure p_odd_even(@i int)
    as
    select number -1 + @i from master..spt_values where type = 'P' and number % 2 = 1 and number <= 100

  3. #3
    Join Date
    Oct 2004
    Location
    Durban - South Africa
    Posts
    13
    Very clever trick - what is the purpose of this table in SQL
    TrevorW
    If at first you don't succeed, call it version 1.0

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    too clever

    you would need DISTINCT in there, vaxman, and also restrict number to between 0 and 100, not just less than or equal to 100

    the use of master..spt_values is a hack

    better to declare an integers table, because it will come in handy in so many other queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Posts
    52
    Not sure what spt_values is for. I have seen it used for things like this. In examining the table, it looks like type 'P' does return distinct integers, but as Rudy says, much better to have your own table. I create one like this:

    select top 8000 id = identity(int,1,1) into Numbers from sysobjects s1, sysobjects s2, sysobjects s3

    (8000 because I usually use it for parsing varchar strings but change for your needs)

    Or you can just generate your 50 numbers on the fly. For 50 numbers this is probably more efficient (no I/O and 50 numbers is small enough not to generate a work table) but for larger counts a real table is better because statistics will be kept for it and indexes used.

    create procedure p_odd_even(@i int)
    as

    select (a0.id + a1.id) + @i id
    FROM
    (select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
    (select 0 id union select 10 union select 20 union select 30 union select 40
    union select 50 union select 60 union select 70 union select 80 union select 90) a1
    order by 1

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could always use:
    Code:
    CREATE PROCEDURE pSSkris
      @arg		INT = 1
    AS
    
    SELECT n
       FROM (SELECT 1 + 10 * tens + ones AS n
          FROM (SELECT 0 AS ones UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
             UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
             UNION SELECT 8 UNION SELECT 9) AS a
          CROSS JOIN (SELECT 0 AS tens UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
             UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
             UNION SELECT 8 UNION SELECT 9) AS b) AS c
       WHERE  n % 2 = 2 - @arg
       ORDER BY n
    
    RETURN
    GO
    -PatP

  7. #7
    Join Date
    Jul 2004
    Posts
    52
    uh Pat, didn't I say that? (but you only need 50 numbers...)

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by vaxman
    uh Pat, didn't I say that? (but you only need 50 numbers...)
    Sort of... You relied on another table outside of the problem definition, but my suggestion was self contained. I suspect that mine will be easier to explain too, but that's a relative kind of thing.

    -PatP

  9. #9
    Join Date
    Jul 2004
    Posts
    52
    I think you missed this part:

    create procedure p_odd_even(@i int)
    as
    select (a0.id + a1.id) + @i id
    FROM
    (select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
    (select 0 id union select 10 union select 20 union select 30 union select 40
    union select 50 union select 60 union select 70 union select 80 union select 90) a1
    order by 1

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, I was only looking at your first posting in my previous comment.

    -PatP

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As an interesting wrinkle, you could also use:
    Code:
    SELECT n
       FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
          FROM (SELECT 0 AS b UNION SELECT        1) AS d0
          CROSS JOIN (SELECT 0 AS b UNION SELECT  2) AS d1
          CROSS JOIN (SELECT 0 AS b UNION SELECT  4) AS d2
          CROSS JOIN (SELECT 0 AS b UNION SELECT  8) AS d3
          CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
          CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
          CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
       ) AS z
       WHERE n BETWEEN 1 AND 100
       ORDER BY n
    -PatP

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As an interesting wrinkle, you could also use:
    Code:
    CREATE PROCEDURE pSSkris2
       @arg		INT = 1
    AS
    
    SELECT n
       FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
          FROM (SELECT 2 - @arg AS b) AS d0
          CROSS JOIN (SELECT 0 AS b UNION SELECT  2) AS d1
          CROSS JOIN (SELECT 0 AS b UNION SELECT  4) AS d2
          CROSS JOIN (SELECT 0 AS b UNION SELECT  8) AS d3
          CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
          CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
          CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
       ) AS z
       WHERE n BETWEEN 1 AND 100
       ORDER BY n
    
    RETURN
    -PatP

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I wonder if you'll get an "A"....
    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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Who wants to explain it? Without an explanation, I'd expect that the code is worthless.

    -PatP

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if it's for a school assignment, i would stringly suggest to sskris to submit the solution in post #2

    that'll get an A+
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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