none
Select * from Table where OrderID = @OrderID or ProductID = @ProductID

    Question

  • Hello,
    I am trying to select all the information that come up from a table.  On the website I have a textbox and I want the user to be able to type in the orderID or the productID and the results will come up for him.

    I tried to do

    CREATE PROCEDURE Test1
    @OrderID int,
    @ProductID int
    AS
    select *
    from table
    where order = @orderID or productid = @productid

    but that will not work as the user will only fill in 1 of those 2 options so I keep getting an error.  What should I fix in my stored procedure?
    Wednesday, October 07, 2009 8:12 PM

Answers

  • Create PROCEDURE Test1
    @Input varchar(10)
    AS
     
    if isnumeric(@input)<>0
    select * 
    from test 
    where order_ID = @Input  
    else
    select * 
    from test WHERE
    product_id = @Input
    
    
    
    exec Test1 1
    Exec test 'A'
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Thursday, October 08, 2009 12:34 AM
  • In your first post, you supplied two arguments to the stored procedure, which I would guess that the front end would know if it's an order id or a product id.  If so, then this should get you the results that you are looking for:

    CREATE TABLE Test1
    (
    	OrderId INT
    	,ProductId CHAR
    )
    
    INSERT INTO Test1
    SELECT 1, 'a'
    UNION SELECT 2, 'b'
    UNION SELECT 3, 'c'
    UNION SELECT 4, 'a'
    UNION SELECT 5, 'b'
    UNION SELECT 6, 'c'
    GO
    
    CREATE  PROCEDURE MyProc
    	@OrderId INT = NULL
    	,@ProductId CHAR  = NULL
    AS
    	SELECT * 
    	FROM Test1 
    	WHERE OrderId = COALESCE(@OrderId, OrderId)
    			AND ProductId = COALESCE(@ProductId, ProductId)
    		
    			
    GO
    
    
    EXEC MyProc NULL, 'b'
    GO
    EXEC MyProc 1, NULL
    GO
    
    GO
    
    
    DROP TABLE Test1
    DROP PROCEDURE MyProc
    
    

    Also the solution that Madhu suggest would work, provided that the product id always contains an alphabetic or other character that would make sure that the Product Id always returns false from the ISNUMERIC function.  If you ever get a Product Id '12345' for example, the procedure would evaluate that as an Order, since it's a number.


    Alternatively uf you could modify the web page to pass it a indicator to signify to search by product or order, you could use that to determine the query to execute
    Thursday, October 08, 2009 12:57 AM

All replies

  • You can pass a table to the procedure. To see how this is done look here: http://www.sommarskog.se/tableparam.html
    Alternatively you can pass an XML string.

    --------------
    StarWind Software developer ( http://www.starwindsoftware.com )
    • Proposed as answer by Paul Svirin Wednesday, October 07, 2009 9:44 PM
    • Unproposed as answer by ElBasha Wednesday, October 07, 2009 11:44 PM
    Wednesday, October 07, 2009 9:44 PM
  • That's not really what I need.

    I have a page that is going to load a gridview of all the data where it matches either the orderID or the productID listed in the textbox (I only have 1 textbox where they can a orderID or productID)

    Can I do something such as

     CREATE PROCEDURE Test1
    @Input
    AS
    select *
    from table
    where orderID = @Input or productid = @Input

    My OrderID is an INT and my productID is a varchar(50)
    Wednesday, October 07, 2009 11:46 PM
  • Create PROCEDURE Test1
    @Input varchar(10)
    AS
     
    if isnumeric(@input)<>0
    select * 
    from test 
    where order_ID = @Input  
    else
    select * 
    from test WHERE
    product_id = @Input
    
    
    
    exec Test1 1
    Exec test 'A'
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Thursday, October 08, 2009 12:34 AM
  • In your first post, you supplied two arguments to the stored procedure, which I would guess that the front end would know if it's an order id or a product id.  If so, then this should get you the results that you are looking for:

    CREATE TABLE Test1
    (
    	OrderId INT
    	,ProductId CHAR
    )
    
    INSERT INTO Test1
    SELECT 1, 'a'
    UNION SELECT 2, 'b'
    UNION SELECT 3, 'c'
    UNION SELECT 4, 'a'
    UNION SELECT 5, 'b'
    UNION SELECT 6, 'c'
    GO
    
    CREATE  PROCEDURE MyProc
    	@OrderId INT = NULL
    	,@ProductId CHAR  = NULL
    AS
    	SELECT * 
    	FROM Test1 
    	WHERE OrderId = COALESCE(@OrderId, OrderId)
    			AND ProductId = COALESCE(@ProductId, ProductId)
    		
    			
    GO
    
    
    EXEC MyProc NULL, 'b'
    GO
    EXEC MyProc 1, NULL
    GO
    
    GO
    
    
    DROP TABLE Test1
    DROP PROCEDURE MyProc
    
    

    Also the solution that Madhu suggest would work, provided that the product id always contains an alphabetic or other character that would make sure that the Product Id always returns false from the ISNUMERIC function.  If you ever get a Product Id '12345' for example, the procedure would evaluate that as an Order, since it's a number.


    Alternatively uf you could modify the web page to pass it a indicator to signify to search by product or order, you could use that to determine the query to execute
    Thursday, October 08, 2009 12:57 AM