Creating Select Query with User Supplied Value?

  • 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

  • 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.

  • 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

  • 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]

  • Thank you,

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

    Thanks for your suggestions...

    Tom

  • 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]

  • 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.

  • All very good suggestions...

    Thank you.

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

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