What is the mistake in stored procedure?

Question

0.00/5 (No votes)

See more:

Hi,

the sql statement,

SQL

SELECT DATE,VD_MAXSPEED AS ALLOWED_SPEED,
OVERSPEED,LOCATION
FROM(

SELECT T1.DEVICE_ID,T1.DATE, T2.OVERSPEED, T1.LOCATION
FROM
 
(SELECT CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [DATE],OSD_DEVICEID AS [DEVICE_ID],
	OSD_SPEED AS [SPEED],OSD_LOCATION AS [LOCATION]
    FROM STS_OVER_SPEED_DETAILS
    WHERE OSD_DEVICEID = 'ST0001'
    AND OSD_UTCTIME BETWEEN '2012-07-02' AND '2012-11-12') AS T1
 
JOIN
 
(SELECT  CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [DATE],OSD_DEVICEID AS [DEVICE_ID],
         MIN(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [START_TIME],
         MAX(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [STOP_TIME],
         MAX( cast(OSD_SPEED as decimal(12,2)))  AS [OVERSPEED]
   FROM  STS_OVER_SPEED_DETAILS
  WHERE OSD_DEVICEID = 'ST0001'
    AND OSD_UTCTIME BETWEEN '2012-07-02' AND '2012-11-12'
GROUP BY CONVERT(VARCHAR(10), OSD_UTCTIME, 121),OSD_DEVICEID) AS T2
 
ON T1.DATE = T2.DATE AND T1.SPEED = T2.OVERSPEED) as [BASE]


INNER JOIN STS_VEHICLE_INFORMATION ON VD_DEVICEID=DEVICE_ID

executed successfully and got result as below,

SQL

DATE           ALLOWED_SPEED       OVERSPEED   LOCATION
----          ----------        ---------    ---------
2012-07-02       30                   40          tvm

2012-09-06       30                   80        kozhikode

2012-10-09       30                   50          idukki

I change the above sql into stored procedure as below,

--------------------------------------------------------

SQL

CREATE PROCEDURE [dbo].[STS_GET_OVER_SPEED_DETAILS_REPORT] 	
@DEVICEID	AS VARCHAR(50),
@OSD_FROM_DATE AS DATETIME,
@OSD_TO_DATE AS DATETIME
AS 
BEGIN

SELECT DATE,VD_VEHICLENUMBER AS VEHICLE_NUMBER,VD_MAXSPEED AS ALLOWED_SPEED,
OVERSPEED,LOCATION
FROM(

SELECT T1.DEVICE_ID,T1.DATE, T2.OVERSPEED, T1.LOCATION
FROM
 
(SELECT CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [DATE],OSD_DEVICEID AS [DEVICE_ID],
	OSD_SPEED AS [SPEED],'('+OSD_LATITUDE+', '+OSD_LONGITUDE+')' AS LOCATION
    FROM STS_OVER_SPEED_DETAILS
    WHERE OSD_DEVICEID = @DEVICEID
    AND OSD_UTCTIME BETWEEN @OSD_FROM_DATE AND @OSD_TO_DATE) AS T1
 
JOIN
 
(SELECT  CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [DATE],OSD_DEVICEID AS [DEVICE_ID],
         MIN(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [START_TIME],
         MAX(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [STOP_TIME],
         MAX( cast(OSD_SPEED as decimal(12,2)))  AS [OVERSPEED]
   FROM  STS_OVER_SPEED_DETAILS
  WHERE OSD_DEVICEID = @DEVICEID
    AND OSD_UTCTIME BETWEEN @OSD_FROM_DATE AND @OSD_TO_DATE
GROUP BY CONVERT(VARCHAR(10), OSD_UTCTIME, 121),OSD_DEVICEID) AS T2
 
ON T1.DATE = T2.DATE AND T1.SPEED = T2.OVERSPEED) as [BASE]


INNER JOIN STS_VEHICLE_INFORMATION ON VD_DEVICEID=@DEVICEID

END

---------------------------------------------------------------------------

But, when execute the sp using,

SQL

EXEC [dbo].[STS_GET_OVER_SPEED_DETAILS_REPORT] 	
'ST0001','2012-07-02','2012-11-12'

giving zero record.

why?

plz help me...

Thanks..

Posted 18-Nov-12 23:04pm

hasbina

Updated 18-Nov-12 23:23pm

v4

Add a Solution

Comments

MT_ 19-Nov-12 5:43am

Did the updated solution work?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MT_ · Accepted Answer · 2012-11-18T23:13:00

Solution 1

In the line below

AND OSD_UTCTIME BETWEEN @OSD_TO_DATE AND @OSD_TO_DATE

Both is TO_DATE !!

Change to

AND OSD_UTCTIME BETWEEN @OSD_FROM_DATE AND @OSD_TO_DATE

And you are good to go.
After OP comment that it still not working.

Change the condition as

SQL

AND CONVERT(VARCHAR(10), OSD_UTCTIME, 121) BETWEEN 
CONVERT(VARCHAR(10), @OSD_FROM_DATE , 121) 
AND CONVERT(VARCHAR(10), @OSD_TO_DATE, 121)

Hope that helps. If it does, mark as answer/upvote.
Milind

Posted 18-Nov-12 23:13pm

MT_

Updated 18-Nov-12 23:52pm

v3

Comments

hasbina 19-Nov-12 5:21am

@Milind
no sir.again same, no records..

why?..

MT_ 19-Nov-12 5:31am

TRy updated solution and see if that helps.

hasbina 19-Nov-12 5:50am

@Milind,
no sir..no records

MT_ 19-Nov-12 5:54am

Did you convert the from and to date in all the where clause? Also update OSD_UTCTIME as shown in the updated query. In short, update the DateTime everywhere to use this convert thing rather than simple column name or parameter.
Also, just check the date you are passing by doing a print.

hasbina 19-Nov-12 5:55am

@Milind
sir, have any datatype problem?

Thanks..

MT_ 19-Nov-12 5:58am

Check the previous comment. convert all dates as shown and try.

hasbina 19-Nov-12 6:05am

@Milind

yes.I did as you said..

but same result.no records return..

Thanks....

hasbina 19-Nov-12 7:12am

@Milind
Thank you sir...

MT_ 19-Nov-12 7:14am

Glad it helped. Do mark it as answer/upvote.