error with COALESCE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

error with COALESCE

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-04-06 : 21:29:48

INSERT INTO rhkprod_tblCartAdjustments (
tblPeopleFK
,cfUserID
,tblProductsFK
,adjusted
,originalQuantity
,adjustedQuantity
)
SELECT NULLIF(@app_user_id, '')
,NULLIF(@cf_user_id, '')
,@tblProductsFK
,1
,@cartQuantity
,@quantity
WHERE COALESCE(NULLIF(@app_user_id, ''), NULLIF(@cf_user_id, '')) IS NOT NULL

I am getting:
Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting the varchar value '1506_18149772' to data type int.

If I comment out the following:

WHERE COALESCE(NULLIF(@app_user_id, ''), NULLIF(@cf_user_id, '')) IS NOT NULL

It works.

@app_user_id is an int, @cf_user_id is a varchar. Is there some sort of conversion going on? Can I cast or convert the @cf_user_id to an int somehow? I don't need the value, just need to know that it is not null.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 22:05:23

1506_18149772 can't be converted to an int because of the underscore. Can you instead convert app_user_id to varchar? Use CONVERT function for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-04-07 : 00:21:49

I see no problem in doing this. For the WHERE clause, I don't need the value of @cf_user_id or @app_user_id, I just need to know if it is null or not.

Why is in trying to cast as an int? Is it something inherent to the NULLIF or COALESCE funstions?

Could I just do:
WHERE (@app_user_id IS NOT NULL OR @cf_user_id IS NOT NULL)

?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 00:23:08

isnt this enough?

WHERE @app_user_id IS NOT NULL
OR @cf_user_id > ''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-04-07 : 01:44:18

I can pass either @app_user_id or @cf_user_id. The one that is not passed will be null.

So should I do the same for both?

Either:
WHERE @app_user_id IS NOT NULL
OR @cf_user_id IS NOT NULL

WHERE @app_user_id > ''
OR @cf_user_id > ''

Which is better?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources