[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

datetime SQL 2000 Stored Procedure EXEC(@sqlStatement)

Posted on 2010-09-09
9
Medium Priority
?
596 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 400 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

649 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