Creating Select Query with User Supplied Value?

Question

Creating Select Query with User Supplied Value?

thomas.hill

Mr or Mrs. 500

Points: 504
More actions
January 28, 2010 at 7:52 am

#215130

Greetings,
I have a background in Access Programming, and within the last year have ventured into SQL. I am currently using SQL SERVER 2008 and have assembled my tables and ran a few reports.
However, I now need to either write a VIEW or other query (or maybe this is accomplished through another means) where I can supply the value for a BETWEEN Statement to retrieve data based on those dates.
For example:
I have a table that captures data for yearly training, and hours associated with that training. When the end of the year comes around, I am required to submit several reports showing various hours, and dates of training between certain, but variable data values.
Further example: I may want to retrieve the data with training hours between 10 and 20. Now I can write that procedure as a VIEW using a between statement, but I also need various other reports showing other hour ranges. When you have several END USERS needing this data, I'd rather have a dialogue box pop up and prompt the user for the data range(s) they are requesting.
In Access, this was accomplished simply by writing "Between [Enter Low Range Here] AND [Enter High Range Here], which caused a pop up box to appear with a data input field. I am looking for the same procedure with SQL.
I run a select query using a DISTINCT statement to SUM the total hours for each employee, but of course, cannot include the training course data since it's not DISTINCT. I then need to use that Total Hour Query to find hours of training between specific ranges.
Some sample data:
Table Name Trainingrecords
Column1 - TrainingrecordID
Column2 - SerialNumber
Column3 - LName
Column4 - FName
Column5 - MI
Column6 - CourseNumberID
Column7 - CourseNumber
Column8 - CourseDescription
Column9 - CourseBeginDate
Column10- CourseEndDate
Column11- CourseHourCredit
From this table I can create a query that returns the TOTAL of the CourseHourCredit. Then from this return, I need to write a select statement that prompts the user for the value ranges.
Query to retrieve SUM of Hours From Trainingrecords table:
SELECT DISTINCT LName, FName, MI, SUM(CourseHourCredit) AS Total
From trainingrecords
(Lets call that table/View: totalhoursperperson)
Then I want to run this user selectable data range against it.
Select *
From totalhoursperperson
Where total >= '?' AND <= '?'
(The ? of course would be the data supplied by the requestor).
Make ANY Sense?
Thank you.
Tom

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andy Steinke Mr or Mrs. 500 Points: 565 More actions · Answer 1

SQL Server is not really an interface platform like Access. Typically you would handle user input and the interface through another programming language, although you could use Access as a front end and connect to SQL Server. I'd recommend implementing the TSQL as parameterized stored procedures.

thomas.hill Mr or Mrs. 500 Points: 504 More actions · Answer 2

Thank you for the reply,

I am integrating SQL with Visual Web Developer so the interface is being written in ASPX or asp.net.

I was hoping there was a simple query I could call up with some VB/ASP statements.

Tom

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

Not really sure how you're presenting this data to the users, but I don't know that you'd need to create a view and then query the view unless that worked out better for performance reasons using an indexed view or some other such thing...

I'd just use a group by and a having clause inside of a stored procedure.

note this has not been checked for syntax etc... If you want a tested solution you'd need to submit some sample data and table definitions as per the first link in my signature...

CREATE PROCEDURE GetUserTrainingHours

@lowRange int,

@highRange int

AS

BEGIN

SELECT LName, FName, MI, SUM(CourseHourCredit) AS Total

From trainingrecords

Group BY LName, FName, MI

HAVING SUM(CourseHourCredit) between @lowrange AND @highRange

END

Then you just call the stored procedure passing in the values you got from the user in your front end whatever that happens to be, VB, Access, ASP whatever...

Hope that makes sense....

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

thomas.hill Mr or Mrs. 500 Points: 504 More actions · Answer 4

Thank you,

I'll give the procedure a try in a few and let you know how it worked.

Thanks for your suggestions...

Tom

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

thomas.hill (1/28/2010)
Thank you for the reply,
I am integrating SQL with Visual Web Developer so the interface is being written in ASPX or asp.net.
I was hoping there was a simple query I could call up with some VB/ASP statements.
Tom

Great, so you'd just build a page that has 2 text boxes on it to get your two values.

Then call your stored procedure passing in the two values. You also want to make sure you're only accepting integer values in those text boxes, and such you don't want to end up like Bobby tables[/url]

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Then you really should consider using stored procedures to encapsulate standard queries that users then pass parameters to to restrict the data returned.

You really don't want to have ad hoc queries in your web code. One of the things you really need to be concerned about is SQL Injection.

thomas.hill Mr or Mrs. 500 Points: 504 More actions · Answer 7

thomas.hill

Mr or Mrs. 500

Points: 504

January 28, 2010 at 8:25 am

#1110078

All very good suggestions...

Thank you.