Dynamic SQL with In Clause

_{Tuesday, December 5, 2017 3:29 PM}

I have some Dynamic SQL that works just fine when there are no spaces and no special characters in the data set.  However, when there is a '.' in a record, I get this error.

Incorrect syntax near '.2'.

Here is my SQL:

DECLARE @PID varchar(100)
SET @PID = 'W.F.2'
 
DECLARE @sql nvarchar(1000)
 
SET @sql = 'SELECT * ' + 
            'FROM [TBL_RAW_DATA] ' + 
            'WHERE PID IN ('+@PID+')'
 
EXEC sp_executesql @sql

I tried to add in a wildcard character and some extra quote characters; nothing seems to work.  What am I doing wrong?

MY BOOK

_{Tuesday, December 5, 2017 3:36 PM | 1 vote}

Hi,

It has to be like this :

DECLARE @PID varchar(100)
SET @PID = 'W.F.2'
 
DECLARE @sql nvarchar(1000)
 
SET @sql = 'SELECT * ' + 
            'FROM [TBL_RAW_DATA] ' + 
            'WHERE PID IN ('''+@PID+''')'
 
EXEC sp_executesql @sql

Please mark as answered, If you feel happy with this answer.

_{Tuesday, December 5, 2017 3:38 PM | 1 vote}

But in your case  you can split the values and use cross apply clause 

SQL Server 2016

SELECT * 
           FROM [TBL_RAW_DATA]           CROSS APPLY (SELECT *FROM string_split(@PID ,'.')) AS d
    

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Tuesday, December 5, 2017 3:39 PM | 1 vote}

Because PID is a varchar, you must build the in list as a list of strings, i.e. with quotes, which must be escaped (by doubling them):

SET @sql = 'SELECT * ' + 
            'FROM [TBL_RAW_DATA] ' + 
            'WHERE PID IN ('''+@PID+''')'  -- notice extra escaped quotes

Note this is assuming @PID contains only a single value.

Otherwise  if it contains multiple values (I assume that's why you have an IN list) you need to ensure that your constructed SQL quotes every value so that it ends up, for example, as:

'WHERE PID IN (''value1'', ''value2'')'

_{Tuesday, December 5, 2017 3:43 PM | 1 vote}

Or

CREATE FUNCTION dbo.TsqlSplit
(@List As varchar(8000))
RETURNS @Items table (Item varchar(8000) Not Null)
AS
BEGIN
  DECLARE @Item As varchar(8000), @Pos As int
  WHILE DATALENGTH(@List)>0
  BEGIN
    SET @Pos=CHARINDEX('.',@List)
    IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
    SET @Item =  LTRIM(RTRIM(LEFT(@List,@Pos-1)))
    IF @Item\<>'' INSERT INTO @Items SELECT @Item
    SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
  END
  RETURN
END
GO

/* Usage example */
 t1

 
declare @inList varchar(50)
set @inList='10428.10429'

 select od.* from [TBL_RAW_DATA] od
 INNER JOIN
         (SELECT Item
            FROM dbo.TsqlSplit(@InList)) As t
        ON od.PID = t.PID 

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Tuesday, December 5, 2017 3:49 PM}

Yes, these all work.  I thought I tried that triple quote!  I guess not though.  One more question.  How can I add in several IDs, separated by commas, or some other character?  Like this:

SET @PID = 'O.D.10,I.A.6,O.D.9'

MY BOOK

_{Tuesday, December 5, 2017 3:55 PM}

Try replace to the single character like a comma comma for example 

SET @PID = REPLACE('O.D.10,I.A.6,O.D.9','.',',')

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Tuesday, December 5, 2017 3:58 PM | 1 vote}

DECLARE @PID varchar(100)
SET @PID = 'O.D.10,I.A.6,O.D.9'



DECLARE @sql nvarchar(1000)
 
SET @sql = 'SELECT * ' + 
            'FROM [TBL_RAW_DATA] ' + 
            'WHERE PID IN (SELECT 
     Split.a.value(''.'', ''VARCHAR(100)'') AS CVS  
FROM  
(
    SELECT CAST (''<M>'' + REPLACE('''+@PID+''', '','', ''</M><M>'') + ''</M>'' AS XML) AS CVS 
) AS A CROSS APPLY CVS.nodes (''/M'') AS Split(a))'
 
EXEC sp_executesql @sql

Please mark as answered, If you feel happy with this answer.

_{Tuesday, December 5, 2017 4:04 PM}

No, that doesn't work.  There must be a way to pass in several variables.  Maybe a loop, but that seems like overkill.

MY BOOK

_{Tuesday, December 5, 2017 4:14 PM}

It is weird.

I tested the sub-query, and it returns the values in rows :

Please mark as answered, If you feel happy with this answer.

_{Tuesday, December 5, 2017 4:22 PM}

That's it!  Thanks Ousama.

MY BOOK

_{Tuesday, December 5, 2017 4:31 PM}

You are welcome.

Please mark the correct answer, that could help other members.

Please mark as answered, If you feel happy with this answer.