Syntax Problem

[Patrick Baird]

Create Procedure sp_InfoDump
(
/* Input Parameters */
@StartDate varchar(12),
@EndDate varchar(12),
@OrderBy varchar(50)
)
As
/* Local variables */
DECLARE @MiD varchar(25)

IF @StartDate = 'ALL DATES'
BEGIN
SELECT @MiD = Min(ATime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MiD = @StartDate
END
SELECT @Statement = 'SELECT tblAudit.*, tblReports.*
FROM tblReports
INNER JOIN tblRAccess ON tblReports.Report_ID = tblRAccess.Report_ID RIGHT OUTER JOIN tblAudit ON tblRAccess.Audit_ID = tblAudit.Audit_ID
WHERE ATime >= ' + @MiD + ' ORDER BY ' + @OrderBy

[Patrick Baird]

the problem occurs when trying to convert the @MiD field.
It doesn't work as a datetime stamp either.

------------
Patrick Baird at 3/12/99 9:09:45 PM

Create Procedure sp_InfoDump
(
/* Input Parameters */
@StartDate varchar(12),
@EndDate varchar(12),
@OrderBy varchar(50)
)
As
/* Local variables */
DECLARE @MiD varchar(25)

IF @StartDate = 'ALL DATES'
BEGIN
SELECT @MiD = Min(ATime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MiD = @StartDate
END
SELECT @Statement = 'SELECT tblAudit.*, tblReports.*
FROM tblReports
INNER JOIN tblRAccess ON tblReports.Report_ID = tblRAccess.Report_ID RIGHT OUTER JOIN tblAudit ON tblRAccess.Audit_ID = tblAudit.Audit_ID
WHERE ATime >= ' + @MiD + ' ORDER BY ' + @OrderBy

[Brian Smith]

Your last line needs to look like this:

WHERE ATime >= ''' + @MiD + ''' ORDER BY ' + @OrderBy

Datetime needs quotes around it.

------------
Patrick Baird at 3/12/99 9:11:09 PM


the problem occurs when trying to convert the @MiD field.
It doesn't work as a datetime stamp either.

------------
Patrick Baird at 3/12/99 9:09:45 PM

Create Procedure sp_InfoDump
(
/* Input Parameters */
@StartDate varchar(12),
@EndDate varchar(12),
@OrderBy varchar(50)
)
As
/* Local variables */
DECLARE @MiD varchar(25)

IF @StartDate = 'ALL DATES'
BEGIN
SELECT @MiD = Min(ATime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MiD = @StartDate
END
SELECT @Statement = 'SELECT tblAudit.*, tblReports.*
FROM tblReports
INNER JOIN tblRAccess ON tblReports.Report_ID = tblRAccess.Report_ID RIGHT OUTER JOIN tblAudit ON tblRAccess.Audit_ID = tblAudit.Audit_ID
WHERE ATime >= ' + @MiD + ' ORDER BY ' + @OrderBy