CODEWITHSUNDEEP
sqlsql-server
Kyle93
Kyle93

Reputation: 795

SQL - User input in query

I have this. "Detail all films shown by the club between any two given dates, inputted by the user. For example a club member must be able to input a start date and an end date for the parameters for the query"

Now, how would I go about doing the user input? Only way I can think of would to be using php or something with a html form getting the values and then submitting them as variables in the query. However, that's not what is needed. So, how is this done so the query would ask for values? Or can't you?

My query so far looks like so.

SELECT film.title, film.desc, show.sdate, show.fdate
FROM film
INNER JOIN show
ON film.ID=show.filmID
WHERE sdate = '&userstart' AND fdate = '&userend'

How do I go about with the user input? Also, is the query correct? I have no way of testing, I only have a design not an implementation.

Thanks a lot

Edit: Using Windows system, MS SQL Server.

Upvotes: 5

Views: 135534

Answers (3)

Jim
Jim

Reputation: 3508

Here's the code for a stored procedure:

CREATE PROCEDURE SomeName(@UserStart DATETIME, @UserEnd DATETIME) 
AS BEGIN

SELECT somestuff
FROM sometable
WHERE somedate BETWEEN @UserStart AND @UserEnd

END

Upvotes: 3

KJB
KJB

Reputation: 11

@Kyle93 - Looking at your WHERE Clause:

WHERE sdate = '&userstart' AND fdate = '&userend'

This will only get you Films that had a sdate(Start Date?) equal to the date value entered. You might want to use Greater than, Less Than operators....

Such as:

WHERE sdate <= '&userend' AND fdate >= '&userstart'

(Note comparing sdate to UserEnd Date to make sure Film Showing started EARLIER than the UserEnd Date...etc)

This way - when a show starts OR ends within the date range - it will be selected.

Upvotes: 1

jos
jos

Reputation: 431

Are you familiar at all with LINQ? It's a way to make db calls from c#.

-- I would handle this with HTML as you thought, and use Javascript/Ajax to reference c# code that uses LINQ for db calls. Might be a bit complicated however, if you're not familiar with much of that. I can recommend good tutorials if interested tho.

Upvotes: -2

Related Questions