如何使用while循环为此sql sp

问题描述

USE[Test2OMS]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<author,,name>
-- Create date: <create>
-- Description:	<description,,>
-- =============================================
CREATE PROCEDURE sp_TruckAlert(@TruckId int )

AS
BEGIN
	declare @Rpath varchar(80)
declare @status int	 = (select DATEDIFF(day, RoadTaxExpiryDate, GETDATE()) from tbl_TruckDetails where TruckID = @TruckId )
 if(@status < -5)
		begin
			Select @Rpath=ImagePath from tbl_AlertImages where ID =4
		end
else if(@status > -3 and @status < -5)
		begin
			Select @Rpath=ImagePath from tbl_AlertImages where ID =2
		end
else if(@status > -1 and @status < -3)
		begin
			Select @Rpath=ImagePath from tbl_AlertImages where ID =3
		end
else if(@status = 1 or @status > 1)
		begin
			Select @Rpath=ImagePath from tbl_AlertImages where ID =1
		end
		------------------------------------------------------------------
declare @Ipath varchar(80)
declare @INstatus int	 = (select DATEDIFF(day, InsuranceExpiryDate, GETDATE()) from tbl_TruckDetails where TruckID = @TruckId)
 if(@INstatus < -5)
		begin
			Select @Ipath=ImagePath from tbl_AlertImages where ID =4
		end
else if(@INstatus >= -3 and @INstatus < -5)
		begin
			Select @Ipath=ImagePath from tbl_AlertImages where ID =2
		end
else if(@INstatus > -1 and @INstatus < -3)
		begin
			Select @Ipath=ImagePath from tbl_AlertImages where ID =3
		end
else if(@INstatus = 1 or @INstatus > 1)
		begin
			Select @Ipath=ImagePath from tbl_AlertImages where ID =1
		end
		-----------------------------------------------------------------
Select t.TruckNo,
		CAST(t.RoadTaxExpiryDate as varchar) as RoadTaxExpiryDate,
		a.ImagePath as RoadPath,
		CAST(t.InsuranceExpiryDate as varchar) as InsuranceExpiryDate,
		b.ImagePath as InsuPath
		--CAST(t.InspectionDate as varchar) as InspectionDate,
		--CAST(t.NextServiceDate as varchar) as NextServiceDate,
		--(t.NextServiceKM - f.OdometerEnd) as NextserviceKM,
		--CAST(NextServiceKM as varchar) as NextServiceKM

	from tbl_TruckDetails t
	Inner Join tbl_FleetFuelMaintenance f on f.TruckID = t.TruckID
	Cross Join tbl_AlertImages a
	Cross Join tbl_AlertImages b
	where t.IsDeleted !=1 and a.ImagePath = @Rpath and t.TruckID = @TruckId
	and b.ImagePath = @Ipath and t.TruckID = @TruckId
END
GO

=============================================== =====================

===================== ===============================================



在这个查询中我每次将TruckId作为参数传递。

我的桌子上有23辆TruckId

怎么能我为所有记录提供循环？



我的问题是我一次只能获得一条记录..



谢谢Advance

====================================================================
====================================================================

In this query im passing every time the TruckId as Parameter.
I have 23 TruckId's in my table
How can i provide loop for all the records?

My problem is im getting only one record at a time..

Thanks Advance

推荐答案

这篇关于如何使用while循环为此sql sp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！