select * from pub_car_infor where ISNULL(customer_guid,CHAR(0))=ISNULL('34b77f55-70ef-4c4b-8f20-4313e5dd0945',CHAR(0)) 提示:将字符串转换为 uniqueidentifier 时失败。(sqlserver)
select * from pub_car_infor where customer_guid='34b77f55-70ef-4c4b-8f20-4313e5dd0945'
不就可以了么?绕那么多干毛?
因为还有别的条件,=null,和is null查出来的结果不一样,我都琢磨了两天了
@bongbang: 你把你条件全列出来。
@Daniel Cai:
DECLARE @VIN NVARCHAR(50)
DECLARE @CAR_NUMBER NVARCHAR(50)
DECLARE @CUSTOMER_GUID NVARCHAR(50)
DECLARE @PHONE NVARCHAR(50)
DECLARE @LINKMAN NVARCHAR(50)
DECLARE @LINKMAN_PHONE NVARCHAR(50)
DECLARE @ID_NUMBER NVARCHAR(50)
DECLARE @CAR_TYPE_GUID NVARCHAR(50)
DECLARE @VIN1 NVARCHAR(50)
DECLARE @ENGINE NVARCHAR(50)
DECLARE @ENGINE_MODEL NVARCHAR(50)
DECLARE @MILEAGE NVARCHAR(50)
DECLARE @STOCK_NAME NVARCHAR(50)
DECLARE @CAR_INFOR_REMARK NVARCHAR(50)
DECLARE @ADDRESS NVARCHAR(50)
DECLARE @MODELS_TYPE_CODE NVARCHAR(50)
DECLARE @BILL_GUID NVARCHAR(50)
DECLARE @OUT_DATE DATETIME
DECLARE @SALES_DATE DATETIME
SET @VIN='C1024824'
SET @CAR_NUMBER='川R51106'
SET @CUSTOMER_GUID= 'cce69c60-f86e-414e-a8be-8659a656c4cf'
SET @PHONE='13679078620'
SET @LINKMAN='易传家'
SET @LINKMAN_PHONE=''
SET @ID_NUMBER=''
SET @CAR_TYPE_GUID=''
SET @VIN1='LGAX2B139C1024824'
SET @ENGINE='川R51106'
SET @ENGINE_MODEL=''
SET @MILEAGE='0'
SET @STOCK_NAME='DFL1120B-KD2E-040-010J'
SET @CAR_INFOR_REMARK=''
SET @ADDRESS='0'
SET @MODELS_TYPE_CODE=''
SET @BILL_GUID='7ddca422-102b-4694-94ab-d52ed9f2ed62'
SET @OUT_DATE=''
SET @SALES_DATE='2013-01-17'
IF NOT EXISTS( SELECT 1 FROM PUB_CAR_INFOR LEFT JOIN PUB_CUSTOMER ON PUB_CUSTOMER.CUSTOMER_GUID = PUB_CAR_INFOR.CUSTOMER_GUID
LEFT JOIN PUB_CAR_TYPE ON PUB_CAR_INFOR.CAR_TYPE_GUID=PUB_CAR_TYPE.CAR_TYPE_GUID
LEFT JOIN PUB_MODELS_TYPE ON PUB_CAR_INFOR.MODELS_TYPE_GUID=PUB_MODELS_TYPE.MODELS_TYPE_GUID
WHERE (UNDERPAN=@VIN OR UNDERPAN IS NULL )AND (CAR_NUMBER=@CAR_NUMBER OR CAR_NUMBER IS NULL ) AND (PUB_CAR_INFOR.CUSTOMER_GUID=@CUSTOMER_GUID OR PUB_CAR_INFOR.CUSTOMER_GUID IS NULL )
AND(PUB_CAR_INFOR.PHONE=@PHONE OR PUB_CAR_INFOR.PHONE IS NULL ) AND (LINKMAN=@LINKMAN OR LINKMAN IS NULL )AND (LINKMAN_PHONE=@LINKMAN_PHONE OR LINKMAN_PHONE IS NULL )
AND (ID_NUMBER=@ID_NUMBER OR ID_NUMBER IS NULL )AND (PUB_CAR_INFOR.CAR_TYPE_GUID=@CAR_TYPE_GUID OR PUB_CAR_INFOR.CAR_TYPE_GUID IS NULL )AND(VIN=@VIN1 OR VIN IS NULL )
AND (ENGINE=@ENGINE OR ENGINE IS NULL )AND (ENGINE_MODEL=@ENGINE_MODEL OR ENGINE_MODEL IS NULL )AND (MILEAGE=@MILEAGE OR MILEAGE IS NULL )
AND (STOCK_NAME=@STOCK_NAME OR STOCK_NAME IS NULL ) AND (OUT_DATE=@OUT_DATE OR OUT_DATE IS NULL )AND (PUB_CAR_INFOR.REMARK=@CAR_INFOR_REMARK OR PUB_CAR_INFOR.REMARK IS NULL )
AND ([ADDRESS]=@ADDRESS OR [ADDRESS] IS NULL )AND (SELL_DATE=@SALES_DATE OR SELL_DATE IS NULL )AND (MODELS_TYPE_CODE=@MODELS_TYPE_CODE OR MODELS_TYPE_CODE IS NULL ))
@bongbang: 这个不就是对的了么? mssql中没有=null这种写法。都是 is null
不过这种样的查询如果数据量大的话就会很慢了。
@Daniel Cai: 如果@CAR_TYPE_GUID='' 的话还是提示:将字符串转换为 uniqueidentifier 时失败。
@bongbang: 这个肯定啊,''这个肯定无法转成字符串(guid)的。
你可以在你那条件前面在加一行
@Car_Type_Guid='' OR....)
使用短路的方式规避这个问题
select * from pub_car_infor where (customer_guid is null or customer_guid='34b77f55-70ef-4c4b-8f20-4313e5dd0945'
谢谢
建议使用上面方法