CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sri Reddy
Sri Reddy

Reputation: 7012

How to convert a varchar value to datatype int in SQL Server 2008 with inner join

I have two lookup tables MyProviders and MyGroups. In my stored procedure, I have a temp table (replaced with an actual table for this example) with data. One column EntityId refers to either provider or a group. EntityTypeId tells me in that temp table if the entity is 1 = Provider or 2 = Group. EntityId can either have numeric GroupId or alphanumeric ExternalProviderId.

I want to check if there is any record in my temp table that has an invalid combination of clientOid + entityid from myprovider and mygroup table.

create table MyProviders 
(
    id int, 
    clientoid varchar(20), 
    externalproviderid varchar(20), 
    name varchar(25)
)

create table MyGroups 
(
    id int, 
    clientoid varchar(20), 
    name varchar(25)
)

create table MyJobDetails 
(
     clientoid varchar(20), 
     entityid varchar(20), 
     entitytypeid int, 
     entityname varchar(30)
)

insert into MyJobDetails values ('M.OID', 'MONYE', 1, 'Mark')
insert into MyJobDetails values ('M.OID', 2, 1, 'Lori')
insert into MyJobDetails values ('M.OID', 2, 2, 'Group 1')
insert into MyJobDetails values ('M.OID', 44444, 2, 'Group 2')

insert into MyProviders values (1, 'M.OID', 'MONY', 'Richard')
insert into MyProviders values (2, 'M.OID', '2', 'Mike')
insert into MyProviders values (3, 'M.OID', '3', 'Lori')

insert into MyGroups values (1, 'M.OID', 'Group 1')
insert into MyGroups values (2, 'M.OID', 'Group 2')

I tried the following query to determine if there is an invalid entity or not.

select 
    COUNT(*) 
from 
    MyJobDetails as jd 
where 
    not exists (select 1 
                from MyProviders as p 
                where p.ClientOID = jd.ClientOID 
                  and p.ExternalProviderID = CAST(jd.EntityId as varchar(20)) 
                  and jd.EntityTypeId = 1)
    and not exists (select 1 
                    from MyGroups as g 
                    where g.ClientOID = jd.ClientOID 
                      and g.Id = jd.EntityId 
                      and jd.EntityTypeId = 2)

This works as expected until I get an alphanumeric data in my temp table that doesn't exist in provider table. I get the following error message:

Conversion failed when converting the varchar value 'MONYE' to data type int.

I have tried to update the solutions mentioned in other threads to use IsNumeric but it didn't work either. In this example, I need to return 1 for one invalid entry of MONYE which doesn't exist either in MyProvider or MyGroup table.

Also, if I can optimize the query in better way to achieve what I want?

Upvotes: 0

Views: 2411

Answers (3)

Vigya
Vigya

Reputation: 142

Try this

select count(*)
from (
    select clientoid,entityid from #MyJobDetails where entitytypeid=1
    except
    select p.ClientOID ,convert(varchar(200),p.ExternalProviderID) from #MyProviders p inner join #MyJobDetails jd on p.ClientOID = jd.ClientOID and p.ExternalProviderID = CAST(jd.EntityId as varchar(20)) where jd.EntityTypeId = 1
    except
    select g.ClientOID,convert(varchar(200),g.Id) from #MyGroups g inner join #MyJobDetails jd on g.ClientOID = jd.ClientOID and g.Id = jd.EntityId where jd.EntityTypeId = 2
)a

Upvotes: 0

DBNull
DBNull

Reputation: 86

In the second AND NOT EXISTS section you compare g.Id, an int, with jd.EntityId, a varchar. Cast the g.Id as a varchar. 

and not exists (select 1 
                from #MyGroups as g 
                where g.ClientOID = jd.ClientOID 
                  and CAST(g.Id AS VARCHAR(20)) = jd.EntityId  
                  and jd.EntityTypeId = 2)

Upvotes: 0

marc_s
marc_s

Reputation: 754598

This is a really bad design in my opinion.

Since you're referencing one out of two tables, you cannot enforce referential integrity.

And having different datatypes for your keys makes things even more horrible.

I would use

  • two separate foreign keys in MyJobDetails - one to MyProvider (varchar(20)) and another one to MyGroup (int)
  • make them both nullable
  • establish a proper foreign key relationship to the referenced table for each of those two

This way, both can be the correct datatype for each referenced table, and you won't need the EntityTypeId column anymore.

As a side note: whenever you use Varchar in SQL Server, whether you're defining a parameter, a variable, or using it in a CAST statement, I would recommend to always explicitly define a length for that varchar.

Or do you know what length this varchar in your conversion here is going to be?

CAST(jd.EntityId as varchar)

Use an explicit length - always - it's just a good, safe practice to employ:

CAST(jd.EntityId as varchar(15))

Upvotes: 1

Related Questions