Loading ...
Sorry, an error occurred while loading the content.

Problem with datetime parameter

Expand Messages
  • rherrmann05@comcast.net
    Hi all, I am having a problem with my datetime parameter I am passing to my SQL statement. Here is the SQL code: CREATE PROCEDURE
    Message 1 of 5 , Jun 20, 2006
    • 0 Attachment
      Hi all,

      I am having a problem with my datetime parameter I am passing to my SQL statement. Here is the SQL code:

      CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1]
      @Category1 varchar(50),
      @StartDate datetime,
      @EndDate datetime
      AS
      SET NOCOUNT ON
      declare @SQL varchar(1000)
      set @Category1 = char(39) + replace(@Category1,',',char(39)+','+char(39)) +char(39)
      set @StartDate = @StartDate
      set @EndDate = @EndDate
      set @SQL = 'SELECT DISTINCT Descript, Products.ProductID FROM Products
      LEFT JOIN SalesReceiptsDetail ON Products.ProductID = SalesReceiptsDetail.ProductID
      INNER JOIN SalesReceipts ON SalesReceiptsDetail.SRID = SalesReceipts.SRID
      WHERE InvoiceDate BETWEEN @StartDate AND @EndDate AND Products.Category1 IN (' + @Category1 + ')
      ORDER BY Descript'
      execute (@SQL)
      GO

      I get this error:
      Must declare the variable '@StartDate'.

      The reason I am using the execute statement is because of the Category1 IN(' + @Category1 + '). You guys helped me with this the other day and it works. Now I want to further enhance my WHERE statement to include a date range.

      Can anyone help me accomplish this?

      Thanks,
      Bob

      [Non-text portions of this message have been removed]
    • Michael Weiss
      Hi Bob, You need to treat your date params just like you do the category param like: CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1] @Category1 varchar(50),
      Message 2 of 5 , Jun 20, 2006
      • 0 Attachment
        Hi Bob,
        You need to treat your date params just like you do the category param
        like:
        CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1]
        @Category1 varchar(50),
        @StartDate datetime,
        @EndDate datetime
        AS
        SET NOCOUNT ON
        declare @SQL varchar(1000)
        set @Category1 = char(39) +
        replace(@Category1,',',char(39)+','+char(39)) +char(39) set @StartDate =
        @StartDate set @EndDate = @EndDate set @SQL = 'SELECT DISTINCT Descript,
        Products.ProductID FROM Products
        LEFT JOIN SalesReceiptsDetail ON Products.ProductID =
        SalesReceiptsDetail.ProductID
        INNER JOIN SalesReceipts ON SalesReceiptsDetail.SRID =
        SalesReceipts.SRID
        WHERE InvoiceDate BETWEEN ' + @StartDate + ' AND ' + @EndDate + ' AND
        Products.Category1 IN (' + @Category1 + ')
        ORDER BY Descript'
        execute (@SQL)
        GO

        Hth,
        Michael

        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com
        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
        rherrmann05@...
        Sent: Tuesday, June 20, 2006 9:00 AM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Problem with datetime parameter

        Hi all,

        I am having a problem with my datetime parameter I am passing to my SQL
        statement. Here is the SQL code:

        CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1]
        @Category1 varchar(50),
        @StartDate datetime,
        @EndDate datetime
        AS
        SET NOCOUNT ON
        declare @SQL varchar(1000)
        set @Category1 = char(39) +
        replace(@Category1,',',char(39)+','+char(39)) +char(39)
        set @StartDate = @StartDate
        set @EndDate = @EndDate
        set @SQL = 'SELECT DISTINCT Descript, Products.ProductID FROM Products
        LEFT JOIN SalesReceiptsDetail ON Products.ProductID =
        SalesReceiptsDetail.ProductID
        INNER JOIN SalesReceipts ON SalesReceiptsDetail.SRID =
        SalesReceipts.SRID
        WHERE InvoiceDate BETWEEN @StartDate AND @EndDate AND
        Products.Category1 IN (' + @Category1 + ')
        ORDER BY Descript'
        execute (@SQL)
        GO

        I get this error:
        Must declare the variable '@StartDate'.

        The reason I am using the execute statement is because of the Category1
        IN(' + @Category1 + '). You guys helped me with this the other day and
        it works. Now I want to further enhance my WHERE statement to include a
        date range.

        Can anyone help me accomplish this?

        Thanks,
        Bob

        [Non-text portions of this message have been removed]





        Yahoo! Groups Links
      • Arnie Rowland
        Use the same concept that you used to pass the @Category variable into the @SQL statement. (For both @StartDate and @EndDate.) In your code, you concatenated
        Message 3 of 5 , Jun 20, 2006
        • 0 Attachment
          Use the same concept that you used to pass the @Category variable into the
          @SQL statement. (For both @StartDate and @EndDate.)

          In your code, you concatenated the VALUE of @Category into the @SQL string,
          your code currently concatenates the literal words @StartDate and @EndDate
          into the @SQL String.

          Here is my suggestion:

          CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1]
          ( @Category1 varchar(50)
          , @StartDate datetime
          , @EndDate datetime
          )
          AS

          SET NOCOUNT ON

          DECLARE @SQL nvarchar(1000)

          SET @Category1 = (replace( @Category1, ',', ''',''') )

          SET @SQL = 'SELECT DISTINCT
          Descript
          , p.ProductID FROM Products p
          LEFT JOIN SalesReceiptsDetail srd
          ON p.ProductID = srd.ProductID
          INNER JOIN SalesReceipts sr
          ON srd.SRID = sr.SRID
          WHERE ( InvoiceDate BETWEEN ''' +
          cast( @StartDate as varchar(11)) + '''
          AND ''' +
          cast( @EndDate as varchar(11)) + '''
          AND p.Category1 IN (''' + @Category1 + ''')
          )
          ORDER BY Descript'

          EXECUTE( @SQL )
          GO

          In order to concatenate the date values, you must first cast (or convert)
          them into strings, and use the 3 single quotes on both sides of the
          @StartDate and @EndDate to cause the date VALUES to be inside single quotes
          in the final string (same as char(39) you used). I usually will have a Query
          Analyzer window open and PRINT @SQL the @SQL string just to see if the
          syntax is correct.

          One thing to remember is that EXEC() or sp_executesql run in a different
          thread, so any variables defined in the calling thread are not known to the
          execution thread. That is why you have to add the VALUES not the variable
          names.

          As a separate note: If you DECLARE @SQL as nvarchar() -instead of varchar(),
          it will be easier for you to start using sp_executesql. Sp_executesql is far
          more robust and adaptable to sophisticated dynamic SQL calls than just using
          EXEC() -but it requires nvarchar() parameters.

          - Arnie Rowland

          "I am a great believer in luck, and I find that the harder I work, the more
          I have of it." - Thomas Jefferson (1743-1826)


          -----Original Message-----
          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
          rherrmann05@...
          Sent: Tuesday, June 20, 2006 9:51 AM
          To: Arnie
          Subject: [SQLQueriesNoCode] Problem with datetime parameter

          Hi all,

          I am having a problem with my datetime parameter I am passing to my SQL
          statement. Here is the SQL code:

          CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1]
          @Category1 varchar(50),
          @StartDate datetime,
          @EndDate datetime
          AS
          SET NOCOUNT ON
          declare @SQL varchar(1000)
          set @Category1 = char(39) + replace(@Category1,',',char(39)+','+char(39))
          +char(39)
          set @StartDate = @StartDate
          set @EndDate = @EndDate
          set @SQL = 'SELECT DISTINCT Descript, Products.ProductID FROM Products
          LEFT JOIN SalesReceiptsDetail ON Products.ProductID =
          SalesReceiptsDetail.ProductID
          INNER JOIN SalesReceipts ON SalesReceiptsDetail.SRID = SalesReceipts.SRID
          WHERE InvoiceDate BETWEEN @StartDate AND @EndDate AND Products.Category1
          IN (' + @Category1 + ')
          ORDER BY Descript'
          execute (@SQL)
          GO

          I get this error:
          Must declare the variable '@StartDate'.

          The reason I am using the execute statement is because of the Category1 IN('
          + @Category1 + '). You guys helped me with this the other day and it works.
          Now I want to further enhance my WHERE statement to include a date range.

          Can anyone help me accomplish this?

          Thanks,
          Bob

          [Non-text portions of this message have been removed]





          Yahoo! Groups Links










          Disclaimer - June 20, 2006
          This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
          This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


          [Non-text portions of this message have been removed]
        • Arnie Rowland
          Michael, You forgot that it is necessary to triple up on the single quotes on both sides of the date variables. As you corrected the @SQL String below, it will
          Message 4 of 5 , Jun 20, 2006
          • 0 Attachment
            Michael,

            You forgot that it is necessary to triple up on the single quotes on both
            sides of the date variables.

            As you corrected the @SQL String below, it will STILL NOT execute.

            - Arnie Rowland

            "I am a great believer in luck, and I find that the harder I work, the more
            I have of it." - Thomas Jefferson (1743-1826)


            -----Original Message-----
            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael Weiss
            Sent: Tuesday, June 20, 2006 12:36 PM
            To: Arnie
            Subject: RE: [SQLQueriesNoCode] Problem with datetime parameter

            Hi Bob,
            You need to treat your date params just like you do the category param
            like:
            CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1]
            @Category1 varchar(50),
            @StartDate datetime,
            @EndDate datetime
            AS
            SET NOCOUNT ON
            declare @SQL varchar(1000)
            set @Category1 = char(39) +
            replace(@Category1,',',char(39)+','+char(39)) +char(39) set @StartDate =
            @StartDate set @EndDate = @EndDate set @SQL = 'SELECT DISTINCT Descript,
            Products.ProductID FROM Products
            LEFT JOIN SalesReceiptsDetail ON Products.ProductID =
            SalesReceiptsDetail.ProductID
            INNER JOIN SalesReceipts ON SalesReceiptsDetail.SRID =
            SalesReceipts.SRID
            WHERE InvoiceDate BETWEEN ' + @StartDate + ' AND ' + @EndDate + ' AND
            Products.Category1 IN (' + @Category1 + ')
            ORDER BY Descript'
            execute (@SQL)
            GO

            Hth,
            Michael

            -----Original Message-----
            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
            rherrmann05@...
            Sent: Tuesday, June 20, 2006 9:00 AM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: [SQLQueriesNoCode] Problem with datetime parameter

            Hi all,

            I am having a problem with my datetime parameter I am passing to my SQL
            statement. Here is the SQL code:

            CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1]
            @Category1 varchar(50),
            @StartDate datetime,
            @EndDate datetime
            AS
            SET NOCOUNT ON
            declare @SQL varchar(1000)
            set @Category1 = char(39) +
            replace(@Category1,',',char(39)+','+char(39)) +char(39)
            set @StartDate = @StartDate
            set @EndDate = @EndDate
            set @SQL = 'SELECT DISTINCT Descript, Products.ProductID FROM Products
            LEFT JOIN SalesReceiptsDetail ON Products.ProductID =
            SalesReceiptsDetail.ProductID
            INNER JOIN SalesReceipts ON SalesReceiptsDetail.SRID =
            SalesReceipts.SRID
            WHERE InvoiceDate BETWEEN @StartDate AND @EndDate AND
            Products.Category1 IN (' + @Category1 + ')
            ORDER BY Descript'
            execute (@SQL)
            GO

            I get this error:
            Must declare the variable '@StartDate'.

            The reason I am using the execute statement is because of the Category1
            IN(' + @Category1 + '). You guys helped me with this the other day and
            it works. Now I want to further enhance my WHERE statement to include a
            date range.

            Can anyone help me accomplish this?

            Thanks,
            Bob

            [Non-text portions of this message have been removed]





            Yahoo! Groups Links











            Yahoo! Groups Links










            Disclaimer - June 20, 2006
            This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
            This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


            [Non-text portions of this message have been removed]
          • rherrmann05@comcast.net
            Michael / Arnie: Thanks for your responses. It works now! Bob ... From: Michael Weiss Hi Bob, You need to treat your date params
            Message 5 of 5 , Jun 20, 2006
            • 0 Attachment
              Michael / Arnie:

              Thanks for your responses. It works now!

              Bob

              -------------- Original message --------------
              From: "Michael Weiss" <mweiss@...>

              Hi Bob,
              You need to treat your date params just like you do the category param
              like:
              CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1]
              @Category1 varchar(50),
              @StartDate datetime,
              @EndDate datetime
              AS
              SET NOCOUNT ON
              declare @SQL varchar(1000)
              set @Category1 = char(39) +
              replace(@Category1,',',char(39)+','+char(39)) +char(39) set @StartDate =
              @StartDate set @EndDate = @EndDate set @SQL = 'SELECT DISTINCT Descript,
              Products.ProductID FROM Products
              LEFT JOIN SalesReceiptsDetail ON Products.ProductID =
              SalesReceiptsDetail.ProductID
              INNER JOIN SalesReceipts ON SalesReceiptsDetail.SRID =
              SalesReceipts.SRID
              WHERE InvoiceDate BETWEEN ' + @StartDate + ' AND ' + @EndDate + ' AND
              Products.Category1 IN (' + @Category1 + ')
              ORDER BY Descript'
              execute (@SQL)
              GO

              Hth,
              Michael

              -----Original Message-----
              From: SQLQueriesNoCode@yahoogroups.com
              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
              rherrmann05@...
              Sent: Tuesday, June 20, 2006 9:00 AM
              To: SQLQueriesNoCode@yahoogroups.com
              Subject: [SQLQueriesNoCode] Problem with datetime parameter

              Hi all,

              I am having a problem with my datetime parameter I am passing to my SQL
              statement. Here is the SQL code:

              CREATE PROCEDURE [dbo].[sp_GetSelectedProducts1]
              @Category1 varchar(50),
              @StartDate datetime,
              @EndDate datetime
              AS
              SET NOCOUNT ON
              declare @SQL varchar(1000)
              set @Category1 = char(39) +
              replace(@Category1,',',char(39)+','+char(39)) +char(39)
              set @StartDate = @StartDate
              set @EndDate = @EndDate
              set @SQL = 'SELECT DISTINCT Descript, Products.ProductID FROM Products
              LEFT JOIN SalesReceiptsDetail ON Products.ProductID =
              SalesReceiptsDetail.ProductID
              INNER JOIN SalesReceipts ON SalesReceiptsDetail.SRID =
              SalesReceipts.SRID
              WHERE InvoiceDate BETWEEN @StartDate AND @EndDate AND
              Products.Category1 IN (' + @Category1 + ')
              ORDER BY Descript'
              execute (@SQL)
              GO

              I get this error:
              Must declare the variable '@StartDate'.

              The reason I am using the execute statement is because of the Category1
              IN(' + @Category1 + '). You guys helped me with this the other day and
              it works. Now I want to further enhance my WHERE statement to include a
              date range.

              Can anyone help me accomplish this?

              Thanks,
              Bob

              [Non-text portions of this message have been removed]

              Yahoo! Groups Links




              [Non-text portions of this message have been removed]
            Your message has been successfully submitted and would be delivered to recipients shortly.