asked on

QBF_Stored Procedure (Date Question)

I am creating a stored procedure for a Query by Form form to load a report. I have set most of the variables correctly and everything works just fine. However, I need to add two date parameters to form a BETWEEN date filter. I put in the code and I get an error message:

SYNTAX ERROR converting DATETIME from Character string. Since this is the FIRST stored procedure I am writing I need some guidance to help me through. Here is the code:

ALTER PROCEDURE QBF_Courses

--Create the Input Parameters

      @Category char(2), @Instructor char(20), @County char(20), @Town char(20), @Course char(12), @Beginning_Date datetime, @Ending_Date datetime

      As
      Declare @SQLString varchar(1000)
      Declare @SelectList varchar(1000)
      Declare @SelectHaving datetime

      SET NOCOUNT ON

      SELECT @SelectList = 'Select * FROM qryCourseReportView'

      SELECT @SelectHaving = + 'HAVING COURSEDATE BETWEEN ''' + Convert(varchar(20), @Beginning_Date) + ''' AND ''' + Convert(varchar(20),@Ending_Date) + ''''
      --SELECT @SelectHaving = + 'HAVING COURSEDATE BETWEEN @Beginning_Date AND @Ending_Date'

--Check the first variable
--Check to see if Category IS NOT NULL
--If NOT NULL , begin to construct the WHERE clause.

            If @Category IS NOT NULL

            Begin

                  SELECT @SQLString = 'WHERE CategoryCode = ''' + @Category + ''''

            END

--Check the second variable
--Check to see if Intructor IS NOT NULL
--IF NOT NULL, continue with the WHERE Clause construction

      IF @Instructor IS NOT NULL

      BEGIN

            IF @SQLString Is NOT NULL

            BEGIN
                  SELECT @SQLString = @SQLSTRING + ' AND INSTCODE = ''' + @Instructor + ''''

           END

            ELSE

                  SELECT @SQLString = 'WHERE INSTCODE = ''' + @Instructor + ''''

      END


--Check the Third variable
--Check to see if County IS NOT NULL
--IF NOT NULL, continue with the WHERE Clause construction

      IF @County IS NOT NULL

      BEGIN

            IF @SQLString IS NOT NULL

            BEGIN

                  SELECT @SQLString = @SQLString + ' AND COUNTY = ''' + @County + ''''

            END

            ELSE
                  SELECT @SQLString = 'WHERE COUNTY = ''' + @County + ''''


      END

--Check the Forth variable
--Check to see if TOWN IS NOT NULL
--IF NOT NULL, continue with the WHERE Clause construction

      IF @Town IS NOT NULL

      BEGIN

            IF @SQLString IS NOT NULL

            BEGIN

                  SELECT @SQLString = @SQLString + ' AND TOWN = ''' + @Town + ''''

            END

            ELSE
                  SELECT @SQLString = 'WHERE TOWN = ''' + @Town + ''''


      END

--Check the Fifth variable
--Check to see if Course IS NOT NULL
--IF NOT NULL, continue with the WHERE Clause construction

      IF @Course IS NOT NULL

      BEGIN

            IF @SQLString IS NOT NULL

            BEGIN

                  SELECT @SQLString = @SQLString + ' AND COURSECODE = ''' + @Course + ''''

            END

            ELSE
                  SELECT @SQLString = 'WHERE COURSECODE = ''' + @Course + ''''


      END

--Concatenate the SELECT and the WHERE clause together
--unless all three parameters are null in which case return
--@SelectList by itself without criteria.

      IF @Category IS NULL AND @Instructor IS NULL AND @County IS NULL AND @Town IS NULL AND @Course IS NULL

      BEGIN
            SELECT @SelectList = @SelectList + ' ' + @SelectHaving

      END

      ELSE

      BEGIN
            SELECT @SelectList = @SelectList + ' ' + @SQLSTRING + ' ' + @SelectHaving
      END

--Execute the SQL statement.
EXECUTE(@SELECTLIST)

ASKER CERTIFIED SOLUTION

namasi_navaretnam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

de_anvil

ASKER

namasi_navaretnam

This does not work as I am still getting the error:

"SYNTAX ERROR converting DATETIME from Character string"

If @Beginning_Date datetime is not null and @Ending_Date datetime is not null
begin

IF @SQLString IS NOT NULL
SELECT @SQLString = @SQLString + ' AND coursedate between ''' + @Beginning_Date + ''' and ''' + @Ending_Date +''''
ELSE
SELECT @SQLString = 'WHERE coursedate between ''' + @Beginning_Date + ''' and ''' + @Ending_Date +''''

end

de_anvil

ASKER

namasi_navaretnam,

I accepted your answer as it did in fact point in the right direction for solving my problem. Just had to add a CONVERT to the code for it to work. Final working code is as follows for those who want to see:

      IF @SQLString IS NOT NULL

      SELECT @SQLString = @SQLString + ' AND COURSEDATE between ''' + CONVERT(varchar(20), @Beginning_Date) + ''' and ''' + CONVERT(varchar(20), @Ending_Date) +''''

      ELSE

      SELECT @SQLString = ' WHERE COURSEDATE between ''' + CONVERT(varchar(20), @Beginning_Date) + ''' and ''' + CONVERT(varchar(20), @Ending_Date) +''''

      END

Thanks for the help. Now all I have to do is create the form and attach the stored procedure to the report, set input parameters for the report and all should be well. At least I hope.