SQL Question : "IN" Operator issue

The IN statement is usually used like:
IN("A","B","C")
This would match A,B or C.
Unless @AppName is returning comma seperated and quoted values in a string it will not fit the use.
I believe you also have to bracket the variable [ ] like ([@AppName])
Try the two section I modified below.



DECLARE @AppName VARCHAR(100)
SET @AppName = "'The Windows Generator', 'The Windows Scott Button'"
PRINT @AppName

SELECT      AppID, AppName, AppDesc,
            'Status' = CASE
                  WHEN  Active = 1 THEN 'Active'                  
                  WHEN  Active = 0 THEN 'Inactive'
                  ELSE 'Active'
            END            
      FROM      tblApp
      WHERE      AppName IN ([@AppName])

Since you're building the list as a string, you will also need to build your SQL statement as a string:

DECLARE @AppName VARCHAR(100), @SQL VARCHAR(1000)
SET @AppName = '''The Windows Generator''' + ',' + '''The Windows Scott Button'''
PRINT @AppName

SET @SQL = 'SELECT      AppID, AppName, AppDesc,
            'Status' = CASE
                  WHEN  Active = 1 THEN ''Active''                  
                  WHEN  Active = 0 THEN ''Inactive''
                  ELSE 'Active'
            END            
      FROM      tblApp
      WHERE      AppName IN (' + @AppName + ')'

EXEC (@SQL)

Nope, this is not working...I am getting the two issues...

first is this line :
SET @AppName = "'The Windows Generator', 'The Windows Scott Button'"

Second issue is at this line :
WHERE      AppName IN ([@AppName])

Error message is Invalid Column name @AppName...it looks like whenever we use the square bracket around the variable name then SQL engine recognize that as a "Column Name" and not as "Variable Name"...so this solution is not working...Thanks for the suggestions though...

Any help/suggestions for resolving this problem would be greatly appreciated.

What you are trying to do requires dynamic SQL.

DECLARE @AppName VARCHAR(100), @SQL varchar(max)
SET @AppName = '''The Windows Generator''' + ',' + '''The Windows Scott Button'''
PRINT @AppName

SET @SQL = 'SELECT      AppID, AppName, AppDesc,
            ''Status'' = CASE
                  WHEN  Active = 1 THEN ''Active''
                  WHEN  Active = 0 THEN ''Inactive''
                  ELSE ''Active''
            END            
      FROM      tblApp
      WHERE      AppName IN (' + @AppName + ')'

EXEC(@SQL)

Select allOpen in new window

I got the solution working with the dynamic SQL String....thanks so much..

But can somebody provide me the solution without using the dynamic SQL String creation...since my company standards won't allow the dynamic SQL strings in the SQL Scripts..Help on this would be really appreciated...Thanks

The value of @AppName will be consider as a single string, not two strings separated by comma. So your query will not fetch records

CREATE THIS FUNCTION

-- THIS FUNCTION SPLITS STRING
CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END


DECLARE @AppName VARCHAR(100)
SET @AppName = 'The Windows Generator' + ',' + 'The Windows Scott Button'
PRINT @AppName

SELECT      AppID, AppName, AppDesc,
            'Status' = CASE
                  WHEN  Active = 1 THEN 'Active'                  
                  WHEN  Active = 0 THEN 'Inactive'
                  ELSE 'Active'
            END            
      FROM      tblApp
      WHERE      AppName IN (SELECT * FROM dbo.fnSplit(@AppName))

Try this

Raj

Sure, I will try this out and let you know. Thank you so much.

try like this

DECLARE  @AppName VARCHAR(100) 

SET @AppName = '''The Windows Generator''' + ',' + '''The Windows Scott Button''' 

SELECT @AppName = Replace(@AppName,'''','') 

PRINT @AppName

;WITH cte 
     AS (SELECT Ltrim(Substring(appname,n,Charindex(',',appname + ',',n) - n)) AS appname 
         FROM   (SELECT @AppName AS appname) t1 
                CROSS JOIN (SELECT NUMBER 
                            FROM   MASTER..spt_values 
                            WHERE  TYPE = 'P') AS numbers(n) 
         WHERE  Substring(',' + appname,n,1) = ',' 
                AND n < Len(appname) + 1) 
SELECT appid, 
       t1.appname, 
       appdesc, 
       'Status' = CASE 
                    WHEN active = 1 THEN 'Active' 
                    WHEN active = 0 THEN 'Inactive' 
                    ELSE 'Active' 
                  END 
FROM   tblApp t1 
       JOIN cte c1 
         ON t1.appname = c1.appname

Select allOpen in new window

Hi,

Your requirement possibly have more than one solution.
 1. Using Dynamic query ( Explained above by matthewspatrick)
 2. Using Common table expression (Explained above by  Sharath_123)
 3. Using Xml Document.
 Since 1 & 2 is explained in the thread , i'm listing the 3rd possible solution here , Try out and choose which u think is best.. :)

DECLARE @XmlDoc NVARCHAR(300)

DECLARE @AppName NVARCHAR(100)
SET @AppName = '''The Windows Generator''' + ',' + '''The Windows Scott Button'''
PRINT @AppName

-- Prepare the document node for Xml document.
SET @XmlDoc =
'<root><r>' + replace(@AppName,',','</r><r>') + '</r></root>'
print @XmlDoc
EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlDoc




SELECT      AppID, AppName, AppDesc,
            'Status' = CASE
                  WHEN  Active = 1 THEN 'Active'                  
                  WHEN  Active = 0 THEN 'Inactive'
                  ELSE 'Active'
            END            
      FROM      tblApp
      WHERE      AppName IN (SELECT CAST([text] AS VARCHAR(MAX))
                                                FROM
                                                OPENXML (@idoc, N'//root/r')
                                                WHERE Text IS NOT NULL  
                                                 )
EXEC sp_xml_removedocument @idoc

You can try using CHARINDEX instead of IN:

DECLARE @AppName VARCHAR(100)
SET @AppName =  '''The Windows Generator''' + ',' + '''The Windows Scott Button'''
PRINT @AppName

-- add delimiters on the start and end so that each list item is bracketed by a delimiter
SET @AppName = ',' + @AppName +  ','

SELECT      AppID, AppName, AppDesc,
            'Status' = CASE
                  WHEN  Active = 1 THEN 'Active'                  
                  WHEN  Active = 0 THEN 'Inactive'
                  ELSE 'Active'
            END            
      FROM      tblApp
      WHERE      CHARINDEX((',' + AppName + ','), @AppName) > 0

Also, no matter what approach you use, if there is any chance of a comma existing in one of your list items, you should consider a different delimiter, such as the pipe |, which is unlikely to naturally exist in text.

Thanks for the solutions, it worked for me.