SQL Server : passing company id (int) with comma in where condition

Question

I am working in SQL Server 2008. I am facing a problem in a where condition, passing multiple company ID values separated by comma (,) to use in Int company ID.

ALTER Procedure
  [dbo].[SP_WorkOrderDetails] @CompanyID varchar(50)
as begin
  Select
    *
  from
    ClientContract C
  left join
    WorkOrder W
  on
    C.Id=W.ClientContractId
  left join
    ClientContractContacts CB
  on
    CB.ClientContractld=C.Id
  left join
    Client CL
  on
    CL.Id= C.Clientld
  left join
    Client CN
  on
    CN.Id= CB.Contactld
  left join
    BaseUnit B
  on
    B.Id=W.BaseUnitId
  left join
    users u
  on
    u.Staffld = W.AssignedTo
  left join
    LatticeERP_DFS..sv_App_Staff SPT
  on
    SPT.Leaderld=W.AssignedTo and
    SPT.Module = 'S'
  left join
    Staff SS
  on
    SS.Id=u.StaffId --and ss.IsTeamLeader = 1
  left join
    Companies Comp
  on
    Comp.id=C.Companyld
  Where
    convert(nvarchar(50),isnull(C.companyID, 1)) in (replace('1,7', '''', ','))
  Order By
    ContractCode,
    WorkOrderNo Desc
end

Don't post code as an image. Post your code as text. An image of code is useless to every other user here. — Thom A, Jul 06 '18 at 08:09
Also, have a search. Asking how to separate a comma separated list in SQL is probably one of the most asked question on SO. Ther's are literally 1,000's of answers to choose from. — Thom A, Jul 06 '18 at 08:13

kiran gadhe · Answer 1 · 2018-07-06T09:38:33.720

0

can you try this

IN  (select cast ( STRING_SPLIT ( '1,7'  , ',' )  as int ) )

or may be this

 IN  ( SELECT 
 Split.a.value('.', 'VARCHAR(100)') AS CVS  
 FROM  
(
SELECT CAST ('<M>' + REPLACE('1,7', ',', '</M><M>') + '</M>' AS XML) AS CVS 
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a) )

edited Jul 06 '18 at 09:38

answered Jul 06 '18 at 09:28

kiran gadhe

733
3
11

1

`STRING_SPLIT` is not available in `SQL SERVER 2008` – Kobi Jul 06 '18 at 09:28

score 0 · Answer 2 · answered Jul 06 '18 at 09:38

you can use this table-valued function:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
create FUNCTION dbo.SplitString(@str VARCHAR(4000),@seprator VARCHAR(1))
RETURNS TABLE
AS
RETURN ( WITH tokens(p,a,b) AS (SELECT 1,1,CHARINDEX(@seprator,@str)
UNION ALL SELECT p+1,b+1,CHARINDEX(@seprator,@str,b+1)
FROM tokens WHERE b>0
)
SELECT
SUBSTRING(@str,a,CASE WHEN b > 0 THEN b-a ELSE 4000 end)
AS VALUE
FROM tokens)

GO

use it like:

... where ID In(select value from dbo.SplitString('1,7,...',','))

SQL Server : passing company id (int) with comma in where condition

2 Answers2