Solved

QBF_Stored Procedure (Date Question)

Posted on 2003-12-08
3
462 Views
Last Modified: 2008-03-04
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)
0
Comment
Question by:de_anvil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 500 total points
ID: 9900424
You will need something like,

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

0
 
LVL 1

Author Comment

by:de_anvil
ID: 9903987
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
0
 
LVL 1

Author Comment

by:de_anvil
ID: 9905264
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.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question