首页 新闻 赞助 找找看

帮帮我帮帮我

0
[待解决问题]

select * from pub_car_infor where  ISNULL(customer_guid,CHAR(0))=ISNULL('34b77f55-70ef-4c4b-8f20-4313e5dd0945',CHAR(0))  提示:将字符串转换为 uniqueidentifier 时失败。(sqlserver)

bongbang的主页 bongbang | 初学一级 | 园豆:190
提问于:2016-11-30 16:33
< >
分享
所有回答(3)
0

select * from pub_car_infor where customer_guid='34b77f55-70ef-4c4b-8f20-4313e5dd0945'

不就可以了么?绕那么多干毛?

Daniel Cai | 园豆:10424 (专家六级) | 2016-11-30 16:46

因为还有别的条件,=null,和is null查出来的结果不一样,我都琢磨了两天了

支持(0) 反对(0) bongbang | 园豆:190 (初学一级) | 2016-11-30 19:32

@bongbang: 你把你条件全列出来。

支持(0) 反对(0) Daniel Cai | 园豆:10424 (专家六级) | 2016-12-01 08:47

@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 ))

支持(0) 反对(0) bongbang | 园豆:190 (初学一级) | 2016-12-02 11:00

@bongbang: 这个不就是对的了么? mssql中没有=null这种写法。都是 is null

不过这种样的查询如果数据量大的话就会很慢了。

支持(0) 反对(0) Daniel Cai | 园豆:10424 (专家六级) | 2016-12-02 11:37

@Daniel Cai: 如果@CAR_TYPE_GUID='' 的话还是提示:将字符串转换为 uniqueidentifier 时失败。

支持(0) 反对(0) bongbang | 园豆:190 (初学一级) | 2016-12-02 11:50

@bongbang: 这个肯定啊,''这个肯定无法转成字符串(guid)的。

你可以在你那条件前面在加一行

@Car_Type_Guid='' OR....)

使用短路的方式规避这个问题

支持(0) 反对(0) Daniel Cai | 园豆:10424 (专家六级) | 2016-12-02 11:55
1

select * from pub_car_infor where (customer_guid is null or customer_guid='34b77f55-70ef-4c4b-8f20-4313e5dd0945'

ζั͡ޓއއއ๓ 堕落 | 园豆:5 (初学一级) | 2016-12-01 09:37

谢谢

支持(0) 反对(0) bongbang | 园豆:190 (初学一级) | 2016-12-01 17:34
0

建议使用上面方法

lwr | 园豆:48 (初学一级) | 2016-12-01 16:39
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册