多条件查询的存储过程的写法

最新推荐文章于 2025-03-19 09:48:26 发布

pmandy

最新推荐文章于 2025-03-19 09:48:26 发布

阅读量145

点赞数

分类专栏：小经验小技巧 .net

.net 同时被 2 个专栏收录

41 篇文章

订阅专栏

小经验小技巧

39 篇文章

订阅专栏

本文介绍了一个用于查询备件归还信息的存储过程SP_RevertOrderInfo。该存储过程可以根据多个条件筛选备件归还记录，包括备件序列号、材料编号等，并返回与备件归还相关的详细信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE [ESD]
GO
/****** Object: StoredProcedure [dbo].[SP_RevertOrderInfo] Script Date: 2013/10/15 15:44:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[SP_RevertOrderInfo]
(
@SPSN varchar(40),
@MaterialNo varchar(40),
@BorrowSPSN varchar(40),
@OrderID bigint,
@SOID bigint,
@PRCode bigint,
@BranchID varchar(40),
@UserID varchar(40),
@StartDate bigint,
@EndDate bigint,
@PageSize int,
@PageIndex int,
@Count int output
)
/******************************************************************************
** Name: SP_GetRevertOrderInfo
** Desc: 查询备件归还信息
** Return Values:SP_GetRevertOrderInfo
** Auth:a
** Date: 2013-05-28
******************************************************************************/
AS
BEGIN
SELECT * FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY [SPOD].[CreateTime] DESC) AS [rowIndex],
[SPO].[RTID],
[SPO].[BranchID],
[SPOD].[SPSN],
[SPOD].[MaterialNo],
[SPOD].[BorrowSPSN],
[SPOD].[SPMark],
(CASE WHEN [IDTS].[MarkStatus] IS NULL THEN '70' ELSE [IDTS].[MarkStatus] END) AS [MarkStatus],
[SM].[MaterialName],
[SM].[YakuanPrice] AS [DownYakuanPrice],
[USM].[YakuanPrice] AS [UpYakuanPrice],
[SA].[StoreAreaName],
[SPOD].[ChangeRecID],
[SPOD].[SOID],
[SPOD].[PRCode],
[SPO].[CreateBy],
[SPO].[CreateTime]
FROM [SP_RevertOrder] AS [SPO]
INNER JOIN [SP_RevertOrderDetail] AS [SPOD] ON [SPO].[RTID] = [SPOD].[RTID]
INNER JOIN [SP_Material] AS [SM] ON [SPOD].[MaterialNo]=[SM].[MaterialNo]
LEFT JOIN [SP_Material] AS [USM] ON [SPOD].[BorrowMaterialNo]=[USM].[MaterialNo]
INNER JOIN [MD_MaterialClass] AS [MC] ON [SM].[MaterialClassCode]=[MC].[MaterialClassCode]
INNER JOIN [SP_StoreArea] AS [SA] ON [SPOD].[StoreAreaID]=[SA].[StoreAreaID]
LEFT JOIN [SP_InventoryDetails] AS [IDTS] ON [SPOD].[SPSN] =[IDTS].[SPSN]
WHERE (@SPSN ='' OR [SPOD].[SPSN]=@SPSN)
AND (@MaterialNo ='' OR [SPOD].[MaterialNo]=@MaterialNo)
AND (@BorrowSPSN ='' OR [SPOD].[BorrowSPSN]=@BorrowSPSN)
AND (@SOID IS NULL OR [SPOD].[SOID]=@SOID)
AND (@PRCode IS NULL OR [SPOD].[PRCode]=@PRCode)
AND [SPO].[BranchID]=@BranchID
AND (@OrderID IS NULL OR [SPO].[RTID]=@OrderID)
AND (@UserID ='' OR [SPOD].[CreateBy]=@UserID)
AND ([SPO].[RTID]>=@StartDate
AND [SPO].[RTID]<=@EndDate)
) AS [RES]
WHERE [RES].[rowIndex]>@PageSize*(@PageIndex-1) AND [RES].[rowIndex]<=@PageSize*@PageIndex

SET @Count=(SELECT COUNT([SPOD].[RTDID])
FROM [SP_RevertOrder] AS [SPO]
INNER JOIN [SP_RevertOrderDetail] AS [SPOD] ON [SPO].[RTID] = [SPOD].[RTID]
INNER JOIN [SP_Material] AS [SM] ON [SPOD].[MaterialNo]=[SM].[MaterialNo]
LEFT JOIN [SP_Material] AS [USM] ON [SPOD].[BorrowMaterialNo]=[USM].[MaterialNo]
INNER JOIN [MD_MaterialClass] AS [MC] ON [SM].[MaterialClassCode]=[MC].[MaterialClassCode]
INNER JOIN [SP_StoreArea] AS [SA] ON [SPOD].[StoreAreaID]=[SA].[StoreAreaID]
LEFT JOIN [SP_InventoryDetails] AS [IDTS] ON [SPOD].[SPSN] =[IDTS].[SPSN]
WHERE (@SPSN ='' OR [SPOD].[SPSN]=@SPSN)
AND (@MaterialNo ='' OR [SPOD].[MaterialNo]=@MaterialNo)
AND (@BorrowSPSN ='' OR [SPOD].[BorrowSPSN]=@BorrowSPSN)
AND (@SOID IS NULL OR [SPOD].[SOID]=@SOID)
AND (@PRCode IS NULL OR [SPOD].[PRCode]=@PRCode)
AND [SPO].[BranchID]=@BranchID
AND (@OrderID IS NULL OR [SPO].[RTID]=@OrderID)
AND (@UserID ='' OR [SPOD].[CreateBy]=@UserID)
AND ([SPO].[RTID]>=@StartDate
AND [SPO].[RTID]<=@EndDate))
RETURN
END