how to use while loop for this sql sp

Hi ,

One more thing you can do is you can pass your truck ids seperated by , comma. and then do looping .

refer this article.
http://www.sqlusa.com/bestpractices/training/scripts/splitcommadelimited/

and also you can use table data type as input and table type as return type. construct all your quesries by applyin union between all of them and then return combined result.

refer this link for table data type and its usage
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/importance-of-while-loop-and-table-variable-in-sql-server/


Regards,
Mubin

1. If you want to send single paremeter but no of values then use csv where u have to make all values in csv and send to sp
2. If you want table as parameter as then you can use TVP which is feature of SQL server 2008 not for SQl server 2005.

I Strongly Feel that by using the relations between the tables, with out using any loops u can write that query by using case Conditions and joins... as i don't know the relations so am using cursor and loop...
Any way u can use this Proc...

USE[Test2OMS] 
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE sp_TruckAlert
AS
BEGIN
   Declare @Rpath varchar(80),@Ipath varchar(80),@Path Varchar(80),@TruckId int
   Declare @status int,@Flag int
   Declare @TruckId_Cursor Cursor
 
   Create Table #Output
          (
	  TruckNo Int,
	  RoadTaxExpiryDate varchar(40),
	  RoadPath Varchar(80),
	  InsuranceExpiryDate Varchar(40),
	  InsuPath Varchar(80)
	  )

    Set @TruckId_Cursor = CURSOR FAST_FORWARD
    For 
    Select Distinct TruckID From tbl_TruckDetails

    Open @TruckId_Cursor
    Fetch Next From @TruckId_Cursor into @TruckId
    While @@FETCH_STATUS=0
    Begin
    	select @status= DATEDIFF(day, RoadTaxExpiryDate, GETDATE()) from tbl_TruckDetails 
               where TruckID = @TruckId 
        Set @Flag=0
		
	While @Flag<2
	Begin
	   if(@status < -5)
	       	Select @Path=ImagePath from tbl_AlertImages where ID =4
	   else if(@status > -3 and @status < -5)
	   	Select @Path=ImagePath from tbl_AlertImages where ID =2
	   else if(@status > -1 and @status < -3)	
	   	Select @Path=ImagePath from tbl_AlertImages where ID =3
	   else if(@status = 1 or @status > 1)	
	   	Select @Path=ImagePath from tbl_AlertImages where ID =1
		
	   If @Flag=1
	       Set @Rpath=@Path
           Else
	     Set @Ipath=@Path
 
	   select @status= DATEDIFF(day, InsuranceExpiryDate, GETDATE()) from tbl_TruckDetails 
                  where TruckID = @TruckId 
	   set @Flag=@Flag+1
	End
		
	Insert into #Output 
	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

	 Fetch Next From @TruckId_Cursor into @TruckId
	End
        Close @TruckId_Cursor
	Deallocate @TruckId_Cursor

    Select * From #Output 
END

USE [Test2OMS]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: krishna
-- Create date: 05-09-2013
-- Description: Alert images Assign to TruckDetails
-- =============================================
Alter PROCEDURE Sp_AlertImages
AS
BEGIN

--============================================================================================
DECLARE @total INT
DECLARE @t INT

DECLARE @TruckAlert TABLE
(
[TruckId] int not null,
[TruckNo] [varchar](10) NOT NULL,
[RoadTaxExpiryDate] [datetime] NOT NULL,
Roadpath varchar(80) null,
[InsuranceExpiryDate] [datetime] NOT NULL,
InsuPath varchar(80) null,
[InspectionDate] [datetime] NOT NULL,
InpPath varchar(80) null,
[NextServiceDate] [datetime] NOT NULL,
NSPath varchar(80) null,
[NextServiceKM] int NOT NULL,
NKPath varchar(80) null


)
SELECT @t = Min(TruckId) FROM tbl_TruckDetails
SELECT @total = Max(TruckId) FROM tbl_TruckDetails
WHILE (@t <= @total)
BEGIN
--SELECT * FROM tbl_TruckDetails WHERE TruckId = @t
---=============================---==========================--======================================-----
declare @Rpath varchar(80)
declare @status int = (select DATEDIFF(day, RoadTaxExpiryDate, GETDATE()) from tbl_TruckDetails where TruckID = @t)
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 = @t)
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
--------------------------------------------------------------------------
declare @Ispath varchar(80)
declare @ISstatus int = (select DATEDIFF(day, InspectionDate, GETDATE()) from tbl_TruckDetails where TruckID = @t)
if(@ISstatus < -5)
begin
Select @Ispath=ImagePath from tbl_AlertImages where ID =4
end
else if(@ISstatus >= -3 and @ISstatus < -5)
begin
Select @Ispath=ImagePath from tbl_AlertImages where ID =2
end
else if(@ISstatus > -1 and @ISstatus < -3)
begin
Select @Ispath=ImagePath from tbl_AlertImages where ID =3
end
else if(@ISstatus = 1 or @ISstatus > 1)
begin
Select @Ispath=ImagePath from tbl_AlertImages where ID =1
end
-----------------------------------------------------------------------------
declare @Nspath varchar(80)
declare @NSstatus int = (select DATEDIFF(day, NextServiceDate, GETDATE()) from tbl_TruckDetails where TruckID = @t)
if(@NSstatus < -5)
begin
Select @Nspath=ImagePath from tbl_AlertImages where ID =4
end
else if(@NSstatus >= -3 and @NSstatus < -5)
begin
Select @Nspath=ImagePath from tbl_AlertImages where ID =2
end
else if(@NSstatus > -1 and @NSstatus < -3)
begin
Select @Nspath=ImagePath from tbl_AlertImages where ID =3
end
else if(@NSstatus = 1 or @NSstatus > 1)
begin
Select @Nspath=ImagePath from tbl_AlertImages where ID =1
end

-----------------------------------------------------------------------------
declare @NKpath varchar(80)
declare @NKstatus int = (select Distinct (tl.NextServiceKM - fl.OdometerEnd) as KM from tbl_TruckDetails tl Inner Join tbl_FleetFuelMaintenance fl on fl.TruckID = tl.TruckID where fl.FleetID=(Select MAX(FleetID) as FleetID from tbl_FleetFuelMaintenance where TruckID=@t) and tl.TruckID=@t)
if(@NKstatus > 500)
begin
Select @NKpath=ImagePath from tbl_AlertImages where ID =4
end
else if(@NKstatus >= 300 and @NKstatus < 500)
begin
Select @NKpath=ImagePath from tbl_AlertImages where ID =2
end
else if(@NKstatus > 1 and @NKstatus < 300)
begin
Select @NKpath=ImagePath from tbl_AlertImages where ID =3
end
else if(@NKstatus = 1 or @NKstatus < -1)
begin
Select @NKpath=ImagePath from tbl_AlertImages where ID =1
end
---------------------------------------------------------------------------------------

INSERT INTO @TruckAlert(TruckId,TruckNo,RoadTaxExpiryDate,Roadpath,InsuranceExpiryDate,InsuPath,InspectionDate,InpPath,NextServiceDate,NSPath,NextServiceKM,NKPath)

Select t.TruckID,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,
c.ImagePath as InpPath,
CAST(t.NextServiceDate as varchar) as NextServiceDate,
d.ImagePath as NSPath,
(t.NextServiceKM - f.OdometerEnd) as NextserviceKM,
e.ImagePath as NKPath
--CAST(NextServiceKM as varchar) as NextServiceKM

from tbl_TruckDetails t
Cross Join tbl_AlertImages a
Cross Join tbl_AlertImages b
Cross Join tbl_AlertImages c
Cross Join tbl_AlertImages d
Cross Join tbl_AlertImages e
Inner Join tbl_FleetFuelMaintenance f on f.TruckID = t.TruckID

where t.IsDeleted !=1 and a.ImagePath = @Rpath and t.TruckID = @t and
f.FleetID=(Select MAX(FleetID) as FleetID from tbl_FleetFuelMaintenance where TruckID=@t)
and b.ImagePath = @Ipath and t.TruckID = @t
and c.ImagePath = @Ispath and t.TruckID = @t
and d.ImagePath = @Nspath and t.TruckID = @t
and e.ImagePath = @NKpath and t.TruckID = @t

SELECT @t = @t + 1
END

select * from @TruckAlert

END
GO