• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

datetime SQL 2000 Stored Procedure EXEC(@sqlStatement)

Dear Experts,

How can I use datetime value in SQL 2000 Stored Procedure EXEC(@sqlStatement)?

I have this Error Message :
Running [dbo].[InventoryTest] ( @whereClause = ProjectNo=N'7812', @startdate = 01/01/10, @enddate = 09/09/10 ).

Syntax error converting datetime from character string.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[InventoryTest].

from Stored Procedure Codes:
ALTER PROCEDURE dbo.InventoryTest
(
      @whereClause nvarchar(4000),
         @startdate datetime,
      @enddate datetime
)
AS
DECLARE @sqlStatement nvarchar(4000)
SET @sqlStatement = '

SELECT     GroupID, ProjectNo, ProductID, ShipDate, BO, USPS, Transfer, FF, Allocated, DM, SM, UPS, Process, Mailed, Received, Adjust, Spoilage
FROM         (SELECT     GroupID, [Project #] AS ProjectNo, [Product #] AS ProductID, ShipDate, BO, USPS, Transfer, FF, Allocated, DM, SM, UPS, Process, Mailed,
                                              0 AS Received, 0 AS Adjust, 0 AS Spoilage
                       FROM          vw_OrderDetails) AS Q2
WHERE     (ShipDate < ' + @startdate + ')'

EXEC(@sqlStatement)

RETURN
0
Takeoutdinner
Asked:
Takeoutdinner
  • 5
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
first, your call is wrong. 01/01/10 is not datetime, but a math expressions, aka 0.1so, this would be the first attempt (which would reply on implicit data type converstion) [dbo].[InventoryTest] ( @whereClause = ProjectNo=N'7812', @startdate = '01/01/10', @enddate = '09/09/10'anyhow, here the code fix
ALTER PROCEDURE dbo.InventoryTest
(
      @whereClause nvarchar(4000),
         @startdate datetime,
      @enddate datetime
)
AS
DECLARE @sqlStatement nvarchar(4000)
SET @sqlStatement = '
SELECT     GroupID, ProjectNo, ProductID, ShipDate, BO, USPS, Transfer, FF, Allocated, DM, SM, UPS, Process, Mailed, Received, Adjust, Spoilage
FROM         (SELECT     GroupID, [Project #] AS ProjectNo, [Product #] AS ProductID, ShipDate, BO, USPS, Transfer, FF, Allocated, DM, SM, UPS, Process, Mailed, 
                                              0 AS Received, 0 AS Adjust, 0 AS Spoilage
                       FROM          vw_OrderDetails) AS Q2
WHERE     (ShipDate < @start_dt)'

EXEC sp_execute_sql @sqlStatement, N'@start_dt datetime', @startdate

Open in new window

0
 
TakeoutdinnerAuthor Commented:
Dear Angel,

I have this error message:

Running [dbo].[InventoryTest] ( @whereClause = ProjectNo=N'7812', @startdate = 01/01/10, @enddate = 09/09/10 ).

Could not find stored procedure 'sp_execute_sql'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[InventoryTest].
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, it must be sp_executesql  ... I had a "_" too much
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
TakeoutdinnerAuthor Commented:
It's good.

Then how can I use BETWEEN AND?
WHERE     (ShipDate BETWEEN @start_dt AND @end_dt)'
EXEC sp_executesql @sqlStatement, N'@start_dt datetime', @startdate, N'@end_dt datetime', @enddate


Thanks,
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

EXEC sp_executesql @sqlStatement, N'@start_dt datetime, @end_dt datetime', @startdate, @enddate
0
 
TakeoutdinnerAuthor Commented:
It's good.

I need to increase @whereClause nvarchar(4000) ==> @whereClause nvarchar(MAX) in SQL 2000.
How can I do it? ==> I will incread Point Value if this solution.

Thanks,

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot do that.
anyhow, a WHERE clause with > 4000 characters somehow shows a design issue ...
0
 
TakeoutdinnerAuthor Commented:
Thanks much for your kind advices.
0
 
TakeoutdinnerAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now