Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: ad-hoc dynamic query with multiple tables

    Hi:
    I'm trying to create an ad-hoc query on a Asp.net page for user. Besides the usual Boolean operators, Field Names, Comparison operators & Field Values, the ad-hoc query also involves multiple tables, eg [Customers] , [Members] & [Orders].

    Now I have difficulties on writing a TSQL sp on how to take the dynamic query with different tables under consideration. User might simply query each individual tables (eg, Customers with age > 25) or combination of tables (eg, Membered Customers with Orders Amt > 1000 between 1/1/2005 - 1/31/2005)

    I have look up a lot dynamic query on the net but all are with only 1 single table to hit. Could anyone give me a direction on how to write a dynamic query script with multiple tables under consideration? Much appreciated.

    ps: The ad-hoc query only contains these defined tables, no other table will be involved.
    Last edited by jasonfisher; 02-08-05 at 07:56.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    First I reccomend you get a book on writing SQL or T-SQL. Second, there is reference material built into your install of SQL Server called Books Online (Start\Programs\Micorsoft SQL Sever\Books Online).

    What you want is a join. To do a join, most of the time you are going to need keys that relate 2 tables. Say you have 2 tables. Orders and OrderDetails. Each will have a primary key that uniquely identifies it. To relate the 2 tables together you need to take the orderid from the orders table (the primary key) and place it in a foriegn key column called orderid in the OrderDetails table. Then you can write a query that looks like this:

    Select Orders.[fieldname1], Orders.[fieldname2], OrdersDetails.[fieldname1], OrdersDetails.[fieldname2]
    FROM Orders JOIN OrderDetails
    ON ORDERS.orderid = OrderDetails.orderid
    WHERE etc......

    or alternatively

    Select Orders.[fieldname1], Orders.[fieldname2], OrdersDetails.[fieldname1], OrdersDetails.[fieldname2]
    FROM Orders,OrderDetails
    WHERE ORDERS.orderid = OrderDetails.orderid
    AND etc......
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why, just use my magic, one-size-fits-all stored procedure:

    Code:
    CREATE PROC blindmansmagicprocedure @SQLStatement(4000)
    AS
    BEGIN
    	EXEC(@SQLStatement)
    END
    GO
    The last stored procedure you will ever need.

    NOT!

    You seriously need to examine the scope of your ad-hoc query application. Just how much power are you going to grant to your users to lock up your database?

    I think you would be better off by creating a stock of views or stored procedures and letting the user choose which them want and apply a filter to it. I'm not sure you realize the size of the task you are undertaking, or all the implications it has for control and maintenance.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    My bad. I just though he did'nt know how to query. I apologize.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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