Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating Select Query with User Supplied Value? Expand / Collapse
Author
Message
Posted Thursday, January 28, 2010 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 2:56 PM
Points: 12, 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


Post #855263
Posted Thursday, January 28, 2010 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 24, 2014 8:30 AM
Points: 17, Visits: 561
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.
Post #855275
Posted Thursday, January 28, 2010 8:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 2:56 PM
Points: 12, 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

Post #855287
Posted Thursday, January 28, 2010 8:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:34 AM
Points: 2,651, Visits: 5,990
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
Post #855297
Posted Thursday, January 28, 2010 8:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:34 AM
Points: 2,651, Visits: 5,990
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
Post #855304
Posted Thursday, January 28, 2010 8:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 2:56 PM
Points: 12, 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
Post #855305
Posted Thursday, January 28, 2010 8:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 20,755, Visits: 32,571
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.



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)
Post #855308
Posted Thursday, January 28, 2010 8:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 2:56 PM
Points: 12, Visits: 26
All very good suggestions...

Thank you.
Post #855313
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse