Comparing varchar value in int type column

Hi,

I have a varchar(255) field on the control_value table which contains 10,159,711. These values are organization_ids (type int) separated by a common.

I am trying to exclude these organization IDs with the following statement:

DECLARE @.exclude_clients varchar(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

select * from organization org

where org.organization_id not in (@.exclude_clients)

I get the following error:

Server: Msg 245, Level 16, State 1, Line 7

Syntax error converting the varchar value '10,159,711' to a column of data type int.

Is there anyway around this?

You couldn't use variables as part of in clause.

But you could use dynamic SQL for with task:

Code Snippet

DECLARE @.exclude_clients varchar(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

declare @.query varchar(1000)

set @.query ='

select * from organization org

where org.organization_id not in ('+@.exclude_clients+')'

EXECUTE(@.query)

Another approach - split @.exclude_clients into table, then use join clause. You could use ideas from this link http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||

You can't use 'NOT IN' in this way - i.e. use it to identify multiple values from a comma-separated list provided as a single parameter.

The NOT IN condition as written will cause the entire @.exclude_clients string to be compared with org.organsation_id. However due to datatype precedence, SQL Server is attempting to convert the string to an integer (i.e. the same datatype as org.organsation_id) before the comparison - which is why you're experiencing the error. If the value of @.exclude_clients was '45' then the query wouldn't cause an error.

One way of performing the task would be to build up your SELECT statement dynamically, see below.

Chris

Code Snippet

DECLARE @.exclude_clients VARCHAR(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

/*

SELECT *

FROM organization org

WHERE org.organization_id NOT IN (@.exclude_clients)

*/

DECLARE @.sql VARCHAR(4000)

SET @.sql = 'SELECT * FROM organisation org WHERE org.organisation_id NOT IN ('

+ @.exclude_clients + ')'

EXEC (@.sql)

|||

Code Snippet

DECLARE @.T1 table(exclude_id int)

insert into @.t1
select 10 union
select 159 union
select 711

or if control_value is a table then

insert into @.t1
select value
from control_value
where parameter= 'client_excluded'

Then you can just use @.t1 as a table in your other query(ies)

select * from organization org
where org.organization_id not in (select exclude_id from @.t1)

|||Thanks Konstantin Kosinsky and Chris Howarth. The dynamic query works fine and it really helpful.