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

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