SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating Select Query with User Supplied Value?


Creating Select Query with User Supplied Value?

Author
Message
thomas.hill
thomas.hill
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 26
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
Andy Steinke
Andy Steinke
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 669
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
thomas.hill
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 26
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
Luke L
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4012 Visits: 6128
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

For better help with performance problems please read this
Luke L
Luke L
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4012 Visits: 6128
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

-Luke.

To help us help you read this

For better help with performance problems please read this
thomas.hill
thomas.hill
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 26
Thank you,

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

Thanks for your suggestions...

Tom
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39748 Visits: 38563
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
thomas.hill
thomas.hill
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 26
All very good suggestions...

Thank you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search