Using Variables and Parameters in a Query for a Date Value

[CTekGirl]

I have 4 small select queries that I running at the same time in Query Analyzer. Each select statement within this SQL code uses a Period End Date. Although the table names are different they all have the same period end date. Instead of editing the program for each closing period I would like to create a variable that would accept a value entered by the user(prompt statement). I could continue to manually edit the end date, but I would like to pass this on to a non-technical user, that does not want to edit SQL statements.

The date format is yyyy-mm-dd

I thought you could use a variable with a parameter for this but perhaps I am missing something. I have included one of the queries as a sample(this does not work, it states that @PAYDATE has not been declared) The code worked before with the period end date hard coded:

DELCARE @PAYDATE DAY
SET @PAYDATE = ?
SELECT DISTINCT TABLE_A.PERENDDATE, TABLE_A.JOBCODE, TABLE_B.JOBCODE, TABLE_B.CHFC_CODE
FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.JOBCODE = TABLE_B.JOBCODE
WHERE (((TABLE_A.PERENDDATE)=@PAYDATE) AND ((TABLE_B.JOBCODE) Is Null) AND ((TABLE_B.CHFC_CODE)<"0" Or (TABLE_B.CHFC_CODE) Is Null));
GO
NOTE:
Each query has a PERENNDATE value. I would like to pass the same date to the variable @PAYDATE and use it throughout the SQL statements.

I used the online books, but I couldn't find exactly what I was looking for. Any assistance would be appreciated.

 

[ArtieChoke]

You need to do this:

declare @PAYDATE datetime
select @PAYDATE = <date value goes here>

if you want to input a parameter, you will need to create a stored procedure. there is no way to prompt for input to a query (that i know of).

if you put the statements above at the beginning of the query, the user can make the modifications easily.