0

how to pass the concatenation String into SQL SELECT IN () ?

DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='1,2'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)

SELECT ADRESSE, AGENCE, AUTRE_REF, CHAUFFEUR, CODE_CLIENT, CODE_DEST, CODE_MAG, CP, CREE_PAR, DATE_CLOTUR, DATE_CREE, DATE_MODIF, EMAIL, ENLEV_CREMB, ENLEV_DECL, ENLEV_UNITE, FACTURATION, FAX, INSEE, LIVRS_EXPRS, LIVRS_SAMD, LIVRS_SIGN, MODAL_MODE, MODAL_PORT, MODAL_SPEC, MODIF_PAR, NBR_COLIS, NO_ORDRE, OBS, PAYS, POID, POID_COR, REF_EXPED, RS_NOM, SIRET, STATUT_ORDRE, TEL, TRANSPORTEUR, VILLE FROM ORDRE WHERE (STATUT_ORDRE = 2) AND (TRANSPORTEUR IN (@NextString))
END

i tried with this, but in not work exactly what i expected.

Thanks you in advance, Stev

user609511
  • 4,091
  • 12
  • 54
  • 86

1 Answers1

3

if you know how many parameters in @NextString, you can use
TRANSPORTEUR IN (@parm1,@parm2,@parm3......)
OR you need to use the exec to execute the sql
declare @sql varchar(max)
set @sql='SELECT ADRESSE, AGENCE, AUTRE_REF,
CHAUFFEUR, CODE_CLIENT, CODE_DEST,
CODE_MAG, CP, CREE_PAR, DATE_CLOTUR,
DATE_CREE, DATE_MODIF, EMAIL,
ENLEV_CREMB, ENLEV_DECL, ENLEV_UNITE, FACTURATION,
FAX, INSEE, LIVRS_EXPRS, LIVRS_SAMD, LIVRS_SIGN,
MODAL_MODE, MODAL_PORT, MODAL_SPEC, MODIF_PAR, NBR_COLIS,
NO_ORDRE, OBS, PAYS, POID, POID_COR, REF_EXPED, RS_NOM, SIRET, STATUT_ORDRE, TEL, TRANSPORTEUR, VILLE FROM ORDRE WHERE (STATUT_ORDRE = 2) AND (TRANSPORTEUR IN (' + @NextString + '))'
exec (@sql)
and you should set the @NextString as '''p1'',''p2'',''p3'''

================================================================================== update on 11-Jan-2013

create the spiting function 

CREATE FUNCTION [dbo].[udf_Split]    
( @Words nvarchar(MAX)    
, @splitStr varchar(50)    
)    
RETURNS @Result_Table TABLE    
(    
[word] nvarchar(max) NULL    
)    
BEGIN    
Declare @TempStr nvarchar(MAX)    
WHILE (CHARINDEX(@splitStr,@Words)>0)    
BEGIN    
Set @TempStr=SUBSTRING(@Words,1,CHARINDEX(@splitStr,@Words)-1)    
Insert into @Result_Table (word) Values (rtrim(ltrim(@TempStr)))    
Set @Words = REPLACE(@Words,@TempStr+@splitStr,'')    
END    
IF(LEN(RTRIM(LTRIM(@Words)))>0 And CHARINDEX(@splitStr,RTRIM(LTRIM(@Words)))=0)    
Begin    
Set @TempStr=@Words    
Insert into @Result_Table (word) Values (rtrim(ltrim(@TempStr)))    
End    
RETURN    
END

then using join instead of using in 

SELECT ADRESSE,
AGENCE,
AUTRE_REF,
CHAUFFEUR,
CODE_CLIENT,
CODE_DEST,
CODE_MAG,
CP,
CREE_PAR,
DATE_CLOTUR,
DATE_CREE,
DATE_MODIF,
EMAIL,
ENLEV_CREMB,
ENLEV_DECL,
ENLEV_UNITE,
FACTURATION,
FAX,
INSEE,
LIVRS_EXPRS,
LIVRS_SAMD,
LIVRS_SIGN,
MODAL_MODE,
MODAL_PORT,
MODAL_SPEC,
MODIF_PAR,
NBR_COLIS,
NO_ORDRE,
OBS,
PAYS,
POID,
POID_COR,
REF_EXPED,
RS_NOM,
SIRET,
STATUT_ORDRE,
TEL,
TRANSPORTEUR,
VILLE
FROM ORDRE a
INNER JOIN udf_split(@NextString, ',') b
ON b.word = a.TRANSPORTEUR
WHERE (STATUT_ORDRE = 2)
Neo
  • 121
  • 3