Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
seagull
Starting Member
4 Posts |
 Posted - 2009-10-30 : 16:24:04
|
| Hello,Working with a vb 6 front endVery new to all this but here is my question(if it makes sense)If I want my program users to extract data from my DB though a series of list options then will I have to write an individual SLQ query(or stored procedure) ready for that particular pattern of enquiry.i.e. My user wants to know how what animals are native species of a particular country,what is the population and description of those animals,what areas of that country can they be found in etc.Even one combination of this type of query would lead to complicated SQL code but hundred and possibly thousandd of queries could be made with different variations.Is there a way of producing perhaps a series of templates and then dropping the individual query items into the code,or am i completely on the wrong track on this one.sorry if this doesn't make sense but I am new to all of this.Jim |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-30 : 16:34:50
|
| If the structure of the result set is constant but just the query criteria changes then the typcial way to achieve that is by using Parameterized Stored Procedures. Any combination of input parameters could be specified and the rest can be ignored or passed in as NULL.Why don't you post two or three possible queries so we can get an idea of what you mean.Be One with the OptimizerTG |
 |
|
|
seagull
Starting Member
4 Posts |
Posted - 2009-10-30 : 17:44:26
|
| Hi TG Thanks for your reply.When you say input "Parameterized Stored Procedures" do you mean some sort of stored sql code that is cabable of somehow taking the unique values presented by the user(say via a list box) and then executing the unique query to access the required data.jim |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-30 : 18:45:02
|
| Yes, to everything you said excpet the "unique query" part. The query would be generic and the WHERE clause would apply different "filters" based on the input parameters. So for instance you could search for invoices based on customer name, or billto address, or contact name, or lineitem products, etc. But the structure of the result set would be the same (same columns returned) no matter what the user seached by.sounds like you may need to do some preliminary research on what Sql Server is and specifically what stored procedures are and how they are used by applications. Is this a personal or school project or did someone hire you to do this work?Is the database you will be working with a MS Sql Server database?Be One with the OptimizerTG |
|
|
|
seagull
Starting Member
4 Posts |
Posted - 2009-11-01 : 07:55:26
|
| Hi TG,Thankyou for your reply.I am working with Access(but I think I could transfer my little project over to sqlServer if needs be.It's a nightschool projectthat basically involved creating a small calculator with VB6,but that little project got me interested in other programs hence these enquiries.I think what I am trying to discover is exactly what can be done with sql code or stored procedures via input from user using these Parameterized Stored Procedures( I dont quite know what they are or how they work but they sound like the thing I am looking for :) :) :)I understand that It would be easy(ish) to place a drop down List on a form and have the User click on the item,then a pre-written sql statement(or calling a stored procedure) would respond to the item request.For example if I had a drop down List with days of the week it would be easy to pre-write sql to say something likeIf List1.Text="monday" Then... initiate the sql query and nose about the Table then produce the result in the grid.But problems arise when there might be more List boxes(or any input criteria on a form).For example If I had three lists,the first being a List of countries,the second being a List of events and the third a List of outcomes a bit like thisList1UKUSAFRANCEGERMANYS ASFRICAList2National Wildlife EventsLocal Wildlife EventsGeneral Wildlife EventsSpecific Animal Wildlife EventsLIST3Policy Result of events if anyCampaign Result of events if anyNumbers attendedGovernment support if anycharity support if any.So the user may for example click on France in List1,General Wildlife event in List2 and Government support if any in List 3.And as you can imagine there will be a different sql query for each pattern of clicking,and if there were more Lists with even more options then the sql requests could get very complicated and messy,and even with a small sample like the above there could be hundreds of different requests made,which if all had to be pre-witten in an individually taylored sense,it would be a nightmare.Thats why I thought this passing of parametres into code or stored procedures might be the answer,where somehow the combination of List items in the users request could be transfered "on the fly" into a pre-written template and the results produced.Is this possible?Is there any tutorial that could help me with this,I have googled this but many of the results are non specific or too general(or too advanced)Any help aprrecited and thanks again for your replySeagul |
|
|
|
seagull
Starting Member
4 Posts |
Posted - 2009-11-04 : 11:29:44
|
| no suggestions ?? :) :) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-04 : 12:20:15
|
quote:
Originally posted by seagull
no suggestions ?? :) :)
find a sample application that uses the technology you will use. Use that as a model for how to proceed. When you have specific sql server question you can post them here.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Query Templates ??