Save a numeric value separated by commas in a variable sql server 2008

Question

Save a numeric value separated by commas in a variable sql server 2008

Navigation

#1 by (2 votes)

1

Greetings to all, I would like to know if any of you have used any method that allows you to store a numerical string separated by commas and then save it in a variable to be used later.

In the following example, what I am trying to do is that the variable @UnitsysId stores the entire string of the numbers, each comma separation corresponds to the id of each catalog of a record. At the time of filtering by the variable does not accept it. Does anyone know how to do this and if it is possible to do so?

declare @unitsysid varchar(max)
set @unitsysid = 120529170,120520370,120536170,120535270,120524770,159254670,108139370,104620670,165324570

select  v.UnitSysId, v.VehicleId as VehicleFleetVehicleID,a.VehicleId as VehicleSVRVehicleId,p.SharedVehicleId as VehicleSharedId
    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 (@unitsysid) order by v.VehicleId asc

It should be mentioned that if I enter the data directly in the query instead of the variable, it shows me without any problem.

select  v.UnitSysId, v.VehicleId as VehicleFleetVehicleID,a.VehicleId as VehicleSVRVehicleId,p.SharedVehicleId as VehicleSharedId
    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 (120529170,120520370,120536170,120535270,120524770,159254670,108139370,104620670,165324570) order by v.VehicleId asc




UnitSysId   VehicleFleetVehicleID   VehicleSVRVehicleId VehicleSharedId
120520370       24803                     35588              20119
120536170       24809                     35594              20125
120535270       24811                     35596              20127
120524770       24813                     35598              20129
120529170       24818                     35603              20134
159254670       31608                     41933              27116
104620670       50398                     44270              29828
108139370       50413                     44285              29844
165324570       52041                     45819              33485

Thank you.

sql sql-server

asked by Ric_hc 21.12.2017 в 21:03

source

1 answer

Language C Functional dynamic memory How can I rename the name of a tab element in materializecss?

score 2 · Answer 1

As @sstan says, the ways to solve your query would be: 1. a function that disaggregates the list and returns for example a table that you can use in inner join , 2. or build a dynamic sentence and execute it by sp_executesql .

However, another solution something more similar to what you expect could be:

declare @unitsysid varchar(max)
set @unitsysid = '120529170,120520370,120536170,120535270,120524770,159254670,108139370,104620670,165324570'
set @unitsysid = ',' + @unitsysid + ','


select  v.UnitSysId, v.VehicleId as VehicleFleetVehicleID,a.VehicleId as VehicleSVRVehicleId,p.SharedVehicleId as VehicleSharedId
    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 CHARINDEX(',' + v.UnitSysId + ',', @unitsysid) > 0
    order by v.VehicleId asc

What we do is first of all make sure that each set is enclosed in commas @unitsysid = ',' + @unitsysid + ',' this is important because then what we do is see if each v.UnitSysId is in the chain of id's @unitsysid . By having the data "locked" between two specific characters, we avoid problems of values included in others, for example the id 123 would be included in 234123 , instead, looking for ,123, there would be no ambiguity. I do not think it's a very performant solution, in that sense I think the ideas mentioned above are better, but at least this way is a little more like what you're trying.

Clarification : If UnitSysId is numeric you should add a CONVERT , for example: CHARINDEX(',' + CONVERT(VARCHAR,v.UnitSysId) + ',', @unitsysid)