SQL query invalid column name

Question

I had following query

DECLARE @category VARCHAR(50) = 'monitor,LCD,Desktop' 
DECLARE @sql nvarchar(4000)

IF @category IS NOT NULL 
BEGIN
SELECT @sql = 'SELECT *, 
CASE 
    WHEN Aging > 90 THEN ''> 90'' 
    ELSE
        CASE 
            WHEN Aging > 60 THEN ''61-90''
            ELSE 
                CASE 
                    WHEN Aging > 30 THEN ''31-60''
                    ELSE ''<=30''
                END
        END
END AS AgingCat, Pct*100 AS Percentage FROM dbo.vwPartnerProductAging

where category IN ('+@category+') 
ORDER BY PartnerName, Category, [Description]'

END

exec sp_executesql @sql, N'@category VARCHAR(50) ', @category

I want show the data from following query by category which had values: Monitor, LCD and Desktop. And i thing the problem in "WHERE blablabla"

I had 2 condition i had try.

first condition :

From the code showed when i declare @category with values its getting error

"Invalid Column name monitor"

"Invalid Column name LCD"

"Invalid Column name Desktop"

second condition :

I make a change for

where category IN ('''+@category+''')

It works if I only add one value, but if I declare @category with more than one value its not showing anything but no error

If I put the values directly on "WHERE blablabla" it works fine.

Working condition :

DECLARE @category VARCHAR(50) = 'monitor,LCD,Desktop' 
DECLARE @sql nvarchar(4000)

IF @category IS NOT NULL 
BEGIN
SELECT @sql = 'SELECT *, 
CASE 
    WHEN Aging > 90 THEN ''> 90'' 
    ELSE
        CASE 
            WHEN Aging > 60 THEN ''61-90''
            ELSE 
                CASE 
                    WHEN Aging > 30 THEN ''31-60''
                    ELSE ''<=30''
                END
        END
END AS AgingCat, Pct*100 AS Percentage FROM dbo.vwPartnerProductAging

where category IN (''Monitor'',''LCD'',''Desktop'') 
ORDER BY PartnerName, Category, [Description]'

END

exec sp_executesql @sql, N'@category VARCHAR(50) ', @category

I wanted to change:

where category IN (''Monitor'',''LCD'',''Desktop'')

to:

where category IN ( ' + @category + ' )

Thanks

possible duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) — Pondlife, May 03 '12 at 07:04

score 0 · Answer 1 · answered May 03 '12 at 07:56

0

you have to change your @category variable from:

DECLARE @category VARCHAR(50) = 'monitor,LCD,Desktop'

to:

DECLARE @category VARCHAR(50) = '''monitor'',''LCD'',''Desktop'''

and then it will work for you also when you use the @category in your @sql as you wrote:

where category IN ('+@category+')

answered May 03 '12 at 07:56

st mnmn

3,555
3
25
32

thanks but i had solve this problem... the answer i had posted, by the way if i use where category IN('+@category+'). the values @category must converted first in my application, i had try that way and its getting more much time. so my solution is convert in sql before calling the values in my application. – tyo May 04 '12 at 06:07

score 0 · Accepted Answer · answered May 04 '12 at 06:01

thanks @st mnmn but i had solve this problem

i'm using function which split the string with separator character " , "

so the input for @category must have values with separator " , "

example input

@category= 'LCD,Monitor,Desktop'

the function i use :

FUNCTION [dbo].[Split] (@sep char(1), @s varchar(2000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
  SELECT 1, 1, CHARINDEX(@sep, @s)
  UNION ALL
  SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
  FROM Pieces
  WHERE stop > 0
)
SELECT pn,
  SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS split
FROM Pieces

)

and in my store procedure :

............... where Category IN (select split from dbo.Split('','',@category))

SQL query invalid column name

2 Answers2