存储过程小程序

定义一个Nums表

IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(numid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,numsdate smalldatetime  NOT NULL,);
go


DECLARE @i AS INT = 1;
BEGIN TRAN
  WHILE @i <= 100000
  BEGIN
    INSERT INTO dbo.Nums VALUES(DATEADD(day, @i - 1, '20090101'));
    SET @i = @i + 1;
  END
COMMIT TRAN
SET NOCOUNT OFF;
GO

 

IF OBJECT_ID('dbo.sp_driving', 'P') IS NOT NULL

  DROP PROC dbo.sp_driving;
GO
 create procedure sp_driving
 @uid as int=10000,
 @fromdate AS DATETIME = '20130201',
 @todate   AS DATETIME = '20130331'
 as
 set nocount on;
 select REC_ID= CASE WHEN ur.REC_ID is null THEN 0 ELSE ur.REC_ID END  ,ACCELERATION=CASE WHEN ur.ACCELERATION is null THEN 0 ELSE ur.ACCELERATION END ,BRAKES=CASE WHEN ur.BRAKES is null THEN 0 ELSE ur.BRAKES END  ,CHARIOTEST=CASE WHEN ur.CHARIOTEST is null THEN '' ELSE ur.CHARIOTEST END  ,DRIVETIME=CASE WHEN ur.DRIVETIME is null THEN 0 ELSE ur.DRIVETIME END  ,HIGHESTSPEED=CASE WHEN ur.HIGHESTSPEED is null THEN 0 ELSE ur.HIGHESTSPEED END  ,MILE=CASE WHEN ur.MILE is null THEN 0 ELSE ur.MILE END  ,OBDFUEL=CASE WHEN ur.OBDFUEL is null THEN 0 ELSE ur.OBDFUEL END  ,OBDMILE=CASE WHEN ur.OBDMILE is null THEN 0 ELSE ur.OBDMILE END  ,SPEED20T40=CASE WHEN ur.SPEED20T40 is null THEN 0 ELSE ur.SPEED20T40 END  ,SPEED40T60=CASE WHEN ur.SPEED40T60 is null THEN 0 ELSE ur.SPEED40T60 END  ,SPEED60T80= CASE WHEN ur.SPEED60T80 is null THEN 0 ELSE ur.SPEED60T80 END  ,SPEEDL20=CASE WHEN ur.SPEEDL20 is null THEN 0 ELSE ur.SPEEDL20 END  ,SPEEDU80=CASE WHEN ur.SPEEDU80 is null THEN 0 ELSE ur.SPEEDU80 END  ,
 n.numsdate as REC_DATE from  dbo.nums as n LEFT JOIN (select * from (select * from USEROBDREC ure where ure.CHARIOTEST=(select uo.CHARIOTEST from UserOBD uo where uo.UID=@uid )) as usre where usre.REC_DATE>=@fromdate and usre.REC_DATE<=@todate)  as ur on n.numsdate=ur.REC_DATE  where n.numsdate>=@fromdate and n.numsdate<=@todate ;
 
 go
 

EXEC dbo.sp_driving
  @uid   = 10019,

  @fromdate = '20130201',

  @todate   = '20130301';