none
Select query

    Question

  • I have a table  'A' with following data. 

    Table 'A':

    id     part       quantity       price

    1      monitor     10          $100.00

    2      cpu            6           $150.00

    3      speakers   4           $25.00

     I have a SP which returns parts by id(Version 1)

    select * from A where id=@id

    Now I want to modify this SP to (Version 2 ) return data based on quantity and Type (either of 1 condition from 3) .If UI don't pass the values for these two parameters it should work as earlier (Version 1). So I'll have two more parameters @Quantity and @Type(1(greater than Quantity) or 2(less than Quantity) or 3(equal to Quantity))

    Any ideas please?


    • Edited by guest369 Sunday, September 09, 2012 9:42 PM
    Sunday, September 09, 2012 9:41 PM

Answers

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on SQL forums. 

    CREATE TABLE Inventory
    (part_nbr CHAR(15) NOT NULL PRIMARY KEY,-- GTIN code
     part_name VARCHAR(25) NOT NULL,
     onhand_qty INTEGER NOT NULL
        CHECK(onhand_qty >= 0),
     unit_price DECIMAL(12,2) NOT NULL
       CHECK (unit_price >= 0.00));

    INSERT INTO Inventory
    VALUES
    ('000000000000001', 'monitor', 10, 100.00),
    ('00000000000002', 'cpu', 6, 150.00),
    ('00000000000003', 'speakers', 4, 25.00);

    >> I have a SP which returns parts by id(Version 1) <<

    Where is the SP header? Did you not know that we do not use SELECT * in production code? Let's try to turn this into SQL again: 

    CREATE PROCEDURE Find_parts(@in_part_nbr CHAR(15))
    AS 
    SELECT part_nbr, part_name, onhand_qty, unit_price
      FROM Inventory WHERE part_nbr = @in_part_nbr;

    >> Now I want to modify this SP to (Version 2) return data based on quantity and Type [sic: this is not a type] (either of 1 condition from 3). If the User Interface does not pass the values for these two parameters it should work as earlier (Version 1). So I'll have two more parameters @in_onhand_qty and @in_control_flg (1(greater than Quantity) or 2(less than Quantity) or 3(equal to Quantity))<< 

    Stop programming and get a book on Software Engineering. Read the parts about “coupling” and “cohesion”; they will be near the front of the book because they are so basic to any kind of programming, not just SQL procedures. Try this without the flag coupling: 

    CREATE PROCEDURE Find_parts
    (@in_part_nbr CHAR(15),
     @in_onhand_qty INTEGER) 
    AS 
    SELECT part_nbr, part_name, unit_price,
           MAX(WHEN onhand_qty > @in_onhand_qty
                THEN onhand_qty ELSE NULL END)
           AS over_onhand_qty,
           MAX(WHEN onhand_qty = @in_onhand_qty
                THEN  onhand_qty ELSE NULL END)
           AS equi_onhand_qty,
           MAX(WHEN onhand_qty > @in_onhand_qty
                THEN  onhand_qty ELSE NULL END) 
           AS under_onhand_qty
     GROUP BY part_nbr, part_name, unit_price 
      FROM Inventory 
     WHERE part_nbr = @in_part_nbr;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, September 10, 2012 12:16 AM

All replies

  • My suggestion -

    -- create table and insert values
    create table t (id int, part varchar(10), quantity int, price money)
    go
    insert into t (id, part, quantity, price) values
    (1, 'monitor', 10, 100.00),
    (2, 'cpu', 6, 150.00),
    (3, 'speakers', 4, 25.00)
    go
    
    -- verify values
    select * from t
    go
    
    -- create store procedure
    create procedure usp_t
    	@id int = null,
    	@quantity int = null,
    	@price money = null
    as
    begin
    	select *
    	from t
    	where id = @id
    	or quantity = @quantity
    	or price = @price
    end;
    go
    
    -- execute the procedure with different parameters
    exec usp_t @id = 2, @quantity = 4, @price = 100;
    exec usp_t @id = 3;
    exec usp_t @quantity = 10;
    exec usp_t @price = 25;


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Sunday, September 09, 2012 10:22 PM
  • Sorry. That is not what I'm looking for. 

    Something like 

    Select * from A

    where id=@id

    and  if @type=1 

           quantity > @quantity

    and if @type=2

           quantity <@quantity

    and if @type=3

           quantity =@quantity



    • Edited by guest369 Sunday, September 09, 2012 10:40 PM
    Sunday, September 09, 2012 10:39 PM
  • Can I write like this ? 

    and ((@type=1 and quantity>@quantity) or (@type=2 and quantity<@quantity)or (@type=3 and quantity=@quantity) or (quantity=quantity))

    Sunday, September 09, 2012 10:56 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on SQL forums. 

    CREATE TABLE Inventory
    (part_nbr CHAR(15) NOT NULL PRIMARY KEY,-- GTIN code
     part_name VARCHAR(25) NOT NULL,
     onhand_qty INTEGER NOT NULL
        CHECK(onhand_qty >= 0),
     unit_price DECIMAL(12,2) NOT NULL
       CHECK (unit_price >= 0.00));

    INSERT INTO Inventory
    VALUES
    ('000000000000001', 'monitor', 10, 100.00),
    ('00000000000002', 'cpu', 6, 150.00),
    ('00000000000003', 'speakers', 4, 25.00);

    >> I have a SP which returns parts by id(Version 1) <<

    Where is the SP header? Did you not know that we do not use SELECT * in production code? Let's try to turn this into SQL again: 

    CREATE PROCEDURE Find_parts(@in_part_nbr CHAR(15))
    AS 
    SELECT part_nbr, part_name, onhand_qty, unit_price
      FROM Inventory WHERE part_nbr = @in_part_nbr;

    >> Now I want to modify this SP to (Version 2) return data based on quantity and Type [sic: this is not a type] (either of 1 condition from 3). If the User Interface does not pass the values for these two parameters it should work as earlier (Version 1). So I'll have two more parameters @in_onhand_qty and @in_control_flg (1(greater than Quantity) or 2(less than Quantity) or 3(equal to Quantity))<< 

    Stop programming and get a book on Software Engineering. Read the parts about “coupling” and “cohesion”; they will be near the front of the book because they are so basic to any kind of programming, not just SQL procedures. Try this without the flag coupling: 

    CREATE PROCEDURE Find_parts
    (@in_part_nbr CHAR(15),
     @in_onhand_qty INTEGER) 
    AS 
    SELECT part_nbr, part_name, unit_price,
           MAX(WHEN onhand_qty > @in_onhand_qty
                THEN onhand_qty ELSE NULL END)
           AS over_onhand_qty,
           MAX(WHEN onhand_qty = @in_onhand_qty
                THEN  onhand_qty ELSE NULL END)
           AS equi_onhand_qty,
           MAX(WHEN onhand_qty > @in_onhand_qty
                THEN  onhand_qty ELSE NULL END) 
           AS under_onhand_qty
     GROUP BY part_nbr, part_name, unit_price 
      FROM Inventory 
     WHERE part_nbr = @in_part_nbr;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, September 10, 2012 12:16 AM
  • Please see Celko's suggestion for the table and the stored proc. I've fixed a few things in that viz. missing CASE keywords, the less than sign for under_onhand_qty and the order of GROUP BY. Is that similar to something you are looking for? You can refer to the use of CASE statement here and build you logic.

    CREATE PROCEDURE Find_parts
     (@in_part_nbr CHAR(15),
      @in_onhand_qty INTEGER) 
     AS 
     SELECT part_nbr, part_name, unit_price,
            MAX(CASE WHEN onhand_qty > @in_onhand_qty
                 THEN onhand_qty ELSE NULL END)
            AS over_onhand_qty,
            MAX(CASE WHEN onhand_qty = @in_onhand_qty
                 THEN  onhand_qty ELSE NULL END)
            AS equi_onhand_qty,
            MAX(CASE WHEN onhand_qty < @in_onhand_qty
                 THEN  onhand_qty ELSE NULL END) 
            AS under_onhand_qty
    FROM Inventory 
      WHERE part_nbr = @in_part_nbr
      GROUP BY part_nbr, part_name, unit_price ;


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Monday, September 10, 2012 3:06 PM