Solved

datetime SQL 2000 Stored Procedure EXEC(@sqlStatement)

Posted on 2010-09-09
9
575 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 143

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 143

Expert Comment

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

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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
 
LVL 143

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 143

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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