Solved

datetime SQL 2000 Stored Procedure EXEC(@sqlStatement)

Posted on 2010-09-09
9
548 Views
Last Modified: 2012-08-13
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
Comment
Question by:Takeoutdinner
  • 5
  • 4
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33637583
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
 

Author Comment

by:Takeoutdinner
ID: 33637658
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33637699
sorry, it must be sp_executesql  ... I had a "_" too much
0
 

Author Comment

by:Takeoutdinner
ID: 33637943
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 33638041

EXEC sp_executesql @sqlStatement, N'@start_dt datetime, @end_dt datetime', @startdate, @enddate
0
 

Author Comment

by:Takeoutdinner
ID: 33638283
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33638341
you cannot do that.
anyhow, a WHERE clause with > 4000 characters somehow shows a design issue ...
0
 

Author Comment

by:Takeoutdinner
ID: 33638391
Thanks much for your kind advices.
0
 

Author Closing Comment

by:Takeoutdinner
ID: 33638400
Thanks!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
A short film showing how OnPage and Connectwise integration works.

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now