> I am curious do you make a SP for each query need. Meaning for example do you have a separate SP for inserts that go into table A, B, or C. Or do you have one single SP that handles all three based upon what is fed into it?
The simple answer is, yes every insert is its own procedure. Yes. every delete is its own procedure. Yes, nearly every update is a procedure. Yep, that's a lot of procedures. Nope, it is not a programming problem. No there is not one procedure to handle alternate procedures. This a good leading question as why I built CFSQLTool -- inserts -- and why each requires it's own procedure. Oh, there are people trying to do schema analysis of meta-data to do one-fit-all queries, but these seem more difficult to manage for support. My approach with CFSQLTool was to make it as easy and as fast as possible to generate procedures. While, the tool does both stored procedure and ad hoc queries using the same techniques, stored procedures should be used for inserts and deletes. CFSQLTool has something called an insert predictor. (I don't know of any other SQL tool that has this feature, it is what got me started writing CFSQLTool in the first place.) The insert predictor looks at the schema for the table and makes a guess based on some simple rules to build a proper insert statement. The insert predictor should always propose a correct insert statement for the table based on the data model. If the table uses identity columns as the primary key, the delete predictor proposes a delete statement. Also, the update predictor overstates a proposed update statement but is accurate for editing. The select predictor has no pre-defined constraints (where clause), these can be added with the user interface. I designed CFSQLTool for programmers. It writes both cfquery and stored procedure style queries using checkboxes to modify code generation. This approach allows code design from the application prospective but still proposes queries based on the schema. Tools that exclusively use the schema for query proposals limit application design. Joseph ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=36 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181806 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
