SQL Question : "IN" Operator issue

Query 1 :

SELECT      AppID, AppName, AppDesc,
            'Status' = CASE
                  WHEN  Active = 1 THEN 'Active'                  
                  WHEN  Active = 0 THEN 'Inactive'
                  ELSE 'Active'
            END            
FROM      tblApp
WHERE      AppName in ('The Windows Generator','The Windows Scott Button')

This above query returns 2 records which is expected....

Query 2 :

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)

But when I used with the input variable then its showing the zero records...why it is so..Am I missing something....I am trying run these queries in the sql server 2005.

Any help on this would be highly appreciated.

      
sridhar_ravvaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
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])
0
rbeadieCommented:
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)
0
sridhar_ravvaAuthor Commented:
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.

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Patrick MatthewsCommented:
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)

Open in new window

0
sridhar_ravvaAuthor Commented:
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
0
Rajkumar GsSoftware EngineerCommented:
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
0
sridhar_ravvaAuthor Commented:
Sure, I will try this out and let you know. Thank you so much.
0
SharathData EngineerCommented:
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

Open in new window

0
csenasaCommented:
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.. :)
0
csenasaCommented:
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
0
rbeadieCommented:
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.
0
rbeadieCommented:
Oops -- you may also need to add quotes to the values in the AppName column:

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sridhar_ravvaAuthor Commented:
Thanks for the solutions, it worked for me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.