SQL query with variable "Where"

Question

I am new with SQL.

How can I write a query, where the Where condition be dependent on a statement which it will be given from a user?

I have this:

SELECT TablePersdaten.Vorname, TablePersdaten.Nachname, TableBezahlung.Datum, TableBezahlung.BelegNr, TableBezahlung.Betrag, Sum(TableBezahlung.Betrag) AS SummevonBetrag
FROM ((TableTeilnehmer INNER JOIN TablePersdaten ON TableTeilnehmer.IDPersdaten = TablePersdaten.IDPersdaten) INNER JOIN TableKurse ON TableTeilnehmer.IDKurs = TableKurse.IDKurs) INNER JOIN TableBezahlung ON TableTeilnehmer.IDTeilnehmer = TableBezahlung.IDStudent
WHERE TableBezahlung.Datum = "VALUE GIVEN FROM USER"
GROUP BY TablePersdaten.Vorname, TablePersdaten.Nachname, TableBezahlung.Datum, TableBezahlung.BelegNr, TableBezahlung.Betrag
ORDER BY TableBezahlung.Datum;

EDIT: I'm using Access 2013, but I'm coding everything myself with SQL-Code. The values should be given through a form.

what rdbms are you using? and what tool is used to receive the user's input? — Mureinik, Aug 15 '14 at 18:01
This looks like it should be set up as a stored procedure with the value passed in as a parameter....... — user2366842, Aug 15 '14 at 18:02
You'll want to use a variable that is assigned a value by the user. As @Mureinik mentioned, you'll need to figure out how to get user input. — hunch_hunch, Aug 15 '14 at 18:03
I'm using Access 2013 and the values should be given through a form. — juanecabellob, Aug 16 '14 at 09:34

programnub112 · Accepted Answer · 2014-08-15T18:10:14.170

Research stored procedures. You can include user input as a parameter and then pass it to a WHERE clause through a declared parameter.

So ideally it would go something like (and beware of the INT part it may have to have a different value that corresponds to table.datum:

CREATE PROCEDURE dbo.Proc1
  @parameter1 INT

AS
BEGIN
SELECT TablePersdaten.Vorname, TablePersdaten.Nachname, TableBezahlung.Datum, TableBezahlung.BelegNr, TableBezahlung.Betrag, Sum(TableBezahlung.Betrag) AS SummevonBetrag
FROM ((TableTeilnehmer INNER JOIN TablePersdaten ON TableTeilnehmer.IDPersdaten = TablePersdaten.IDPersdaten) INNER JOIN TableKurse ON TableTeilnehmer.IDKurs = TableKurse.IDKurs) INNER JOIN TableBezahlung ON TableTeilnehmer.IDTeilnehmer = TableBezahlung.IDStudent
WHERE TableBezahlung.Datum = @parameter1
GROUP BY TablePersdaten.Vorname, TablePersdaten.Nachname, TableBezahlung.Datum, TableBezahlung.BelegNr, TableBezahlung.Betrag
ORDER BY TableBezahlung.Datum;
END

And of course execute the procedure after creation:

EXEC dbo.Proc1 '@parameter1value'

score 0 · Answer 2 · answered Aug 15 '14 at 18:04

If you parameterize the input "VALUE GIVEN FROM USER" that might be what you're after.

...
WHERE TableBezahlung.Datum = &UserValue
...

The single '&' will substitute that value once. If you use '&&', it will substitute that value through the end of your session.

SQL query with variable "Where"

2 Answers2

Linked