Link to home
Start Free TrialLog in
Avatar of Takeoutdinner
Takeoutdinner

asked on

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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

Avatar of Takeoutdinner
Takeoutdinner

ASKER

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].
sorry, it must be sp_executesql  ... I had a "_" too much
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,
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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,

you cannot do that.
anyhow, a WHERE clause with > 4000 characters somehow shows a design issue ...
Thanks much for your kind advices.
Thanks!