如何使用while循环为此sql sp [英] how to use while loop for this 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

推荐答案

I相信你需要传递一个价值表。请参阅用户定义的表格类型

I believe you need to pass in a table of values. See "User Defined Table Types"

您好，



您可以做的另一件事是您可以将您的卡车ids分开通过，逗号。然后循环播放。



参考这篇文章。

http://www.sqlusa.com/bestpractices/training/scripts/splitcommadelimited/



你也可以使用表数据类型作为输入，使用表类型作为返回类型。通过在所有问题之间的applyin union构建所有问题，然后返回合并结果。



参考此链接获取表数据类型及其用法

http：// www。 c-sharpcorner.com/UploadFile/skumaar_mca/importance-of-while-loop-and-table-variable-in-sql-server/





问候，

Mubin

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。如果你想发送单个参数但没有值，那么使用csv，你必须在csv中创建所有值并发送到sp

2.如果你想将表作为参数，那么你可以使用TVP SQL Server 2008的功能不适用于SQl server 2005。

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.

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