CREATE PROCEDURE [dbo].[up_Service_TSI_Part_Detail]@service_report_no NVARCHAR(20),
@service_order_no NVARCHAR(20),
@borrow_no NVARCHAR(20),
@dealer_id INT,
--@product_id INT,
@model_id INT,
@serial_no NVARCHAR(20),
@part_no NVARCHAR(20),
@part_no_new NVARCHAR(20),
@create_date_start NVARCHAR(10),
@create_date_end NVARCHAR(10),
@borrow_part_seq_id INT,
@userId INTAS
BEGIN SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER ( ORDER BY A.create_date DESC ) AS row_num ,
A.service_report_id ,
A.service_report_no ,
A.create_date ,
B.service_order_id ,
B.service_order_no ,
C.borrow_part_seq_id ,
C.borrow_no ,
-- B.product_id ,
-- B.product_name,
B.model_id ,
B.model_name ,
B.serial_no ,
B.dealer_id ,
B.dealer_name,
B.cust_name ,
B.service_start_date ,
B.service_end_date ,
C.part_no ,
C.part_name ,
C.supplier_id ,
C.steel_grade_no ,
C.qty ,
C.part_no_new ,
C.part_name_new ,
C.supplier_id_new ,
C.steel_grade_no_new ,
C.locale_dispose_qty ,
C.return_qty ,
C.scrap_value_qty ,
C.return_place_name ,
C.re ,
C.borrow_part_seq_id
FROM dbo.Service_Tsi_Report A
INNER JOIN dbo.View_Service_Order B ON A.service_order_id = B.service_order_id
INNER JOIN dbo.View_Service_TSI_Report_Part_Detail C ON A.service_report_id = C.service_report_id
WHERE A.service_report_no LIKE ISNULL(@service_report_no,'')+'%' AND B.service_order_no LIKE ISNULL(@service_order_no,'')+'%'
AND C.borrow_no LIKE ISNULL(@borrow_no,'')+'%' AND (B.dealer_id = @dealer_id OR ISNULL(@dealer_id,0)=0)
-- AND (B.product_id =@product_id OR ISNULL(@product_id,0)=0)
AND (B.model_id=@model_id OR ISNULL(@model_id,0)=0)
AND B.serial_no LIKE ISNULL(@serial_no,'')+'%' AND C.part_no LIKE ISNULL(@part_no,'')+'%'
AND C.part_no_new LIKE ISNULL(@part_no_new,'')+'%'
AND CONVERT(NVARCHAR(10),A.create_date,120) BETWEEN @create_date_start AND @create_date_end
AND (C.borrow_part_seq_id=@borrow_part_seq_id OR ISNULL(@borrow_part_seq_id,0)=0)
AND EXISTS(SELECT 1 FROM dbo.Comm_System_User_Dealer WHERE user_id=@userId AND dealer_id=A.dealer_id)
ORDER BY A.create_date DESCEND
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO各位大侠,AND (B.dealer_id = @dealer_id OR ISNULL(@dealer_id,0)=0)这一句中我所遇到的问题:
1.如果@dealer_id的值与B.dealer_id相等的话,是不是OR后面的就不执行了?
2.如果@dealer_id的值与B.dealer_id不相等并且不为null,则B.dealer_id = @dealer_id是false,ISNULL(@dealer_id,0)=0也为false,那么这个AND的查询条件是不是什么也查不到了?
3.如果@dealer_id的值为null的话,B.dealer_id = @dealer_id为false,ISNULL(@dealer_id,0)=0为true,那这时AND的这个查询又是怎么办?sqlserver
@service_order_no NVARCHAR(20),
@borrow_no NVARCHAR(20),
@dealer_id INT,
--@product_id INT,
@model_id INT,
@serial_no NVARCHAR(20),
@part_no NVARCHAR(20),
@part_no_new NVARCHAR(20),
@create_date_start NVARCHAR(10),
@create_date_end NVARCHAR(10),
@borrow_part_seq_id INT,
@userId INTAS
BEGIN SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER ( ORDER BY A.create_date DESC ) AS row_num ,
A.service_report_id ,
A.service_report_no ,
A.create_date ,
B.service_order_id ,
B.service_order_no ,
C.borrow_part_seq_id ,
C.borrow_no ,
-- B.product_id ,
-- B.product_name,
B.model_id ,
B.model_name ,
B.serial_no ,
B.dealer_id ,
B.dealer_name,
B.cust_name ,
B.service_start_date ,
B.service_end_date ,
C.part_no ,
C.part_name ,
C.supplier_id ,
C.steel_grade_no ,
C.qty ,
C.part_no_new ,
C.part_name_new ,
C.supplier_id_new ,
C.steel_grade_no_new ,
C.locale_dispose_qty ,
C.return_qty ,
C.scrap_value_qty ,
C.return_place_name ,
C.re ,
C.borrow_part_seq_id
FROM dbo.Service_Tsi_Report A
INNER JOIN dbo.View_Service_Order B ON A.service_order_id = B.service_order_id
INNER JOIN dbo.View_Service_TSI_Report_Part_Detail C ON A.service_report_id = C.service_report_id
WHERE A.service_report_no LIKE ISNULL(@service_report_no,'')+'%' AND B.service_order_no LIKE ISNULL(@service_order_no,'')+'%'
AND C.borrow_no LIKE ISNULL(@borrow_no,'')+'%' AND (B.dealer_id = @dealer_id OR ISNULL(@dealer_id,0)=0)
-- AND (B.product_id =@product_id OR ISNULL(@product_id,0)=0)
AND (B.model_id=@model_id OR ISNULL(@model_id,0)=0)
AND B.serial_no LIKE ISNULL(@serial_no,'')+'%' AND C.part_no LIKE ISNULL(@part_no,'')+'%'
AND C.part_no_new LIKE ISNULL(@part_no_new,'')+'%'
AND CONVERT(NVARCHAR(10),A.create_date,120) BETWEEN @create_date_start AND @create_date_end
AND (C.borrow_part_seq_id=@borrow_part_seq_id OR ISNULL(@borrow_part_seq_id,0)=0)
AND EXISTS(SELECT 1 FROM dbo.Comm_System_User_Dealer WHERE user_id=@userId AND dealer_id=A.dealer_id)
ORDER BY A.create_date DESCEND
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO各位大侠,AND (B.dealer_id = @dealer_id OR ISNULL(@dealer_id,0)=0)这一句中我所遇到的问题:
1.如果@dealer_id的值与B.dealer_id相等的话,是不是OR后面的就不执行了?
2.如果@dealer_id的值与B.dealer_id不相等并且不为null,则B.dealer_id = @dealer_id是false,ISNULL(@dealer_id,0)=0也为false,那么这个AND的查询条件是不是什么也查不到了?
3.如果@dealer_id的值为null的话,B.dealer_id = @dealer_id为false,ISNULL(@dealer_id,0)=0为true,那这时AND的这个查询又是怎么办?sqlserver
逻辑上是,但实际执行次序可能不是这样的2.如果@dealer_id的值与B.dealer_id不相等并且不为null,则B.dealer_id = @dealer_id是false,ISNULL(@dealer_id,0)=0也为false,那么这个AND的查询条件是不是什么也查不到了?
这条数据不符合条件而已,其他符合条件的还是会显示,不是什么也查不到3.如果@dealer_id的值为null的话,B.dealer_id = @dealer_id为false,ISNULL(@dealer_id,0)=0为true,那这时AND的这个查询又是怎么办?
如果@dealer_id的值为null的话,所有记录都符合条件,显示出表中符合其他条件的所有记录
2、如果不相等也不为null的话,你的语句就相当于where 2=1,就查不到数据了
3、与问题2一样,如果是null那么查询条件始终为true,相当于没有条件,如 where 1=1
“所有记录都符合条件,显示出表中符合其他条件的所有记录”是什么意思?
我可不可以理解为:AND(B.dealer_id = @dealer_id OR ISNULL(@dealer_id,0)=0)这个查询条件无效了呢?
所以只看其他条件
那么整个where条件and false就全都是false了,所以是查不到数据的第三条中,如果为null值,那么第一个判断始终为false,一定会判断isnull这个条件
又被isnull函数转换为了0=0那么条件始终为true,就相当于where 1=1
始终为true与始终为false的条件基本没什么意义
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Description: 服务维修用件明细
-- =============================================
ALTER PROCEDURE [dbo].[up_Service_TSI_Part_Detail]@service_report_no NVARCHAR(20),
@service_order_no NVARCHAR(20),
@borrow_no NVARCHAR(20),
@dealer_id INT,
--@product_id INT,
@model_id INT,
@serial_no NVARCHAR(20),
@part_no NVARCHAR(20),
@part_no_new NVARCHAR(20),
@create_date_start NVARCHAR(10),
@create_date_end NVARCHAR(10),
@borrow_part_seq_id INT,
@userId INTAS
BEGIN SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER ( ORDER BY A.create_date DESC ) AS row_num ,
A.service_report_id ,
A.service_report_no ,
A.create_date ,
B.service_order_id ,
B.service_order_no ,
C.borrow_part_seq_id ,
C.borrow_no ,
-- B.product_id ,
-- B.product_name,
B.model_id ,
B.model_name ,
B.serial_no ,
B.dealer_id ,
B.dealer_name,
B.cust_name ,
B.service_start_date ,
B.service_end_date ,
C.part_no ,
C.part_name ,
C.supplier_id ,
D.supplier_no AS supplier_name ,
C.steel_grade_no ,
C.qty ,
C.part_no_new ,
C.part_name_new ,
C.supplier_id_new ,
E.supplier_no AS supplier_name_new ,
C.steel_grade_no_new ,
C.locale_dispose_qty ,
C.return_qty ,
C.scrap_value_qty ,
C.return_place_name ,
C.re ,
C.borrow_part_seq_id
FROM dbo.Service_Tsi_Report A
INNER JOIN dbo.View_Service_Order B ON A.service_order_id = B.service_order_id
INNER JOIN dbo.View_Service_TSI_Report_Part_Detail C ON A.service_report_id = C.service_report_id
LEFT JOIN dbo.Comm_Supplier D ON C.supplier_id=D.supplier_id
LEFT JOIN dbo.Comm_Supplier E ON C.supplier_id_new=E.supplier_id
WHERE A.service_report_no LIKE ISNULL(@service_report_no,'')+'%' AND B.service_order_no LIKE ISNULL(@service_order_no,'')+'%'
AND C.borrow_no LIKE ISNULL(@borrow_no,'')+'%' AND (B.dealer_id = @dealer_id OR ISNULL(@dealer_id,0)=0)
-- AND (B.product_id =@product_id OR ISNULL(@product_id,0)=0)
AND (B.model_id=@model_id OR ISNULL(@model_id,0)=0)
AND B.serial_no LIKE ISNULL(@serial_no,'')+'%' AND C.part_no LIKE ISNULL(@part_no,'')+'%'
AND C.part_no_new LIKE ISNULL(@part_no_new,'')+'%'
AND CONVERT(NVARCHAR(10),A.create_date,120) BETWEEN @create_date_start AND @create_date_end
AND (C.borrow_part_seq_id=@borrow_part_seq_id OR ISNULL(@borrow_part_seq_id,0)=0)
AND EXISTS(SELECT 1 FROM dbo.Comm_System_User_Dealer WHERE user_id=@userId AND dealer_id=A.dealer_id)
ORDER BY A.create_date DESCEND
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO