How to convert a string to int SQL Server (Store Procedure)

Question

Good day to everybody! My question is simple, I develop a SQL sp, it needs a string to caracters

USE[Fleet]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [sp_extraccion_sharedIds] @UnitSysId varchar(max)   
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    select v.UnitSysId, v.VehicleId,a.VehicleId,p.SharedVehicleId
    from Fleet..Vehicles v with  (nolock)
        join AVLStatic..Vehicles  a with  (nolock) on v.UnitSysId=a.RMUId
        join PAI..PAIVehicleShared p with  (nolock) on v.VehicleId=p.FleetVehicleId
    where v.UnitSysId in (convert(int, @UnitSysId))
    order by v.UnitSysId asc
END
GO

The SP Works like that:

EXEC sp_extraccion_sharedIds  '141298070,141292370,185244770,185246770,252979770,256986470,256991970,256923170,256919270'

But when it runs, it show me this messsage:

Msg 245, Level 16, State 1, Procedure sp_extraccion_sharedIds, Line 13
Conversion failed when converting the varchar value '141298070,141292370,185244770,185246770,252979770,256986470,256991970,256923170,256919270' to data type int.

Can you help me please? I've try to use cast, but I don't know what is my problem.

Thanks

**[Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause)** Probably duplicate. Consider using TVP instead of varchar(max) with csv values. — Lukasz Szozda, Aug 01 '17 at 16:22
try using `('+@UnitSysId+')` in place of `(convert(int, @UnitSysId))` — Rahul Hendawe, Aug 01 '17 at 17:10

score 1 · Accepted Answer · answered Aug 01 '17 at 16:22

1

You can't do that, you need to split the string into individual rows then CAST it to INT

select v.UnitSysId, v.VehicleId,a.VehicleId,p.SharedVehicleId
from Fleet..Vehicles v with  (nolock)
    join AVLStatic..Vehicles  a with  (nolock) on v.UnitSysId=a.RMUId
    join PAI..PAIVehicleShared p with  (nolock) on v.VehicleId=p.FleetVehicleId
where UnitSysId  in (select value from string_split(@UnitSysId,','))
order by v.UnitSysId asc

If UnitSysId is of INT type then you don't have to do explicit conversion

I have used STRING_SPLIT function introduced in Sql Server 2016 to split the csv into individual rows. For older versions check this answer

answered Aug 01 '17 at 16:22

Pரதீப்

91,748
19
131
172

@Ric_hc - What exactly you did ? Did you get any error message ? Which version of Sql Server you are using ? – Pரதீப் Aug 01 '17 at 16:33
I'm using string_split, the var @UnitSysId is int, but, the arguments are int, I needs separate it in ','. – Ric_hc Aug 01 '17 at 16:33
Ohhh your're right!! Excuse me. My SQL versión is 2008. sorry! – Ric_hc Aug 01 '17 at 16:34
@Ric_hc - Create the function from the link I have shared and use it instead of `string_split` – Pரதீப் Aug 01 '17 at 16:35

How to convert a string to int SQL Server (Store Procedure)

1 Answers1