Question about comma seperated fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question about comma seperated fields

Author

Topic

mauich123
Starting Member

21 Posts

Posted - 2010-12-05 : 18:36:19

I'm quite new to SQL Server so I would love to get some help with this :)

What I'm trying to do is to get all articles from a register. There are several tables linked togeter for example. Article ArticleColor Color and Category. From VB.NET i've created a dataset that runs a stored procedure with various parameters. What I want to do is for each article, get BOTH the color of that item AND all possible colors in a comma seperated list. Here's the SP:

Declare @ColorList varchar(255)

SELECT @ColorList=Coalesce(@ColorList + ', ', '') + Cast(Color.Name AS varchar(15))

FROM Article INNER JOIN ArticleColorWay ON Article.ID = ArticleColorWay.ArticleID INNER JOIN Color ON ArticleColor.ColorID = Color.ColorNo INNER JOIN ArticleCategory ON Article.Category=ArticleCategory.Number
WHERE Article.category = @Cat

SELECT @ColorList, Color.Name as ColorName, Article.Name AS ArticleName

FROM Article INNER JOIN ArticleColorWay ON Article.ID = ArticleColorWay.ArticleID INNER JOIN Color ON ArticleColor.ColorID = Color.ColorNo INNER JOIN ArticleCategory ON Article.Category=ArticleCategory.Number

WHERE Article.category = @Cat

The problem I'm gettin here is that I get ALL colors of the category. As said I'm quite new to SQL so the problem might be obvious :) Please help me!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-05 : 18:53:36

The Field list which is comma separated is being read as a literal string, not comma separated values. You have to use dynamic SQL or otherwise separate the comma values into a 1 colum list to use them as a where condition.

Dynamic SQL isn't the best option.
But you could use what you have by making it dynamic as follows


Declare @ColorList varchar(255)
Declare @SQL varchar(3000)
SELECT @ColorList=Coalesce(@ColorList + ', ', '') + Cast(Color.Name AS varchar(15))

FROM Article INNER JOIN ArticleColorWay ON Article.ID = ArticleColorWay.ArticleID INNER JOIN Color ON ArticleColor.ColorID = Color.ColorNo INNER JOIN ArticleCategory ON Article.Category=ArticleCategory.Number
WHERE Article.category = @Cat

Select @SQL = 'SELECT '+@ColorList + ', Color.Name as ColorName, Article.Name AS ArticleName
FROM Article INNER JOIN ArticleColorWay ON Article.ID = ArticleColorWay.ArticleID INNER JOIN Color ON ArticleColor.ColorID = Color.ColorNo INNER JOIN ArticleCategory ON Article.Category=ArticleCategory.Number
WHERE Article.category = ' + convert(varchar,@Cat)

Exec (@SQL)

Note..without table structure DDL or expected results, the above syntax may not work for you, but essentially dynamic SQL is building the select statement in a string, then execute that string as a SQL statement using EXECUTE

Poor planning on your part does not constitute an emergency on my part.

mauich123
Starting Member

21 Posts

Posted - 2010-12-06 : 04:31:40

Ok I've tried this now but I get the error "Incorrect syntax near Blue", Blue being the first color in the ColorList

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources