SELECT STUFF(a.[description],1,1,'')

FROM (

SELECT '/'+dbo.classstructure.description AS [description]

FROM dbo.classancestor

INNER JOIN dbo.classstructure

...

try something like this

SELECT STUFF(a.[description],1,1,'')

FROM (

SELECT '/'+dbo.classstructure.description

FROM dbo.classancestor

INNER JOIN dbo.classstructure

...

If you do have to use a categories table (and I agree that you do), how about something like this,

which uses a categories table, but with a sort of...

I think what the OP is getting at is a replacement for this structure:

--= some very high performance sql set based code

--....

--....

IF @result = 1

BEGIN

SET...

There is no reason in sql why you cannot put a parameter in the WHERE clause...

..... WHERE Department=@Dept ... for example as long as your reporting tool can handle that...

--= set up some test data

declare @ids table(id int identity(1,1))

insert @ids default values

insert @ids default values

insert @ids default values

insert @ids default values

insert @ids default values

insert @ids default values

insert @ids...

the execution plan says it all:

With the WHERE clause:

|--Filter(WHERE: (CONVERT(decimal(10,5),substring(CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0),[Expr1006],charindex(',',CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0)+',',[Expr1006])-[Expr1006]),0)>(1.00000) AND [Expr1006]<=CONVERT_IMPLICIT(bigint,CONVERT(int,len(CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0)),0),0) AND substring(','+CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0),[Expr1006],(1))=','))

---

Without the WHERE clause:

|--Filter(WHERE: ([Expr1006]<=CONVERT_IMPLICIT(bigint,CONVERT(int,len(CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0)),0),0) AND substring(','+CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0),[Expr1006],(1))=','))

The "s>1" predicate is being "inlined" into the string splitting function...

select *

from names

where TheName like '%[ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýþÿ]%'

Why mess about with functions and stuff?

Justin James (1/4/2010)

---

There is a reason -- the reason is that the solution that I posted above WORKS, albeit slowly. I tried your solution, and I have performance issues with...

Justin,

I think you may still have some work to do as the change you made will be excluding the final grouping - I think...

moving the ISNULL the way you have...

I agree that is HORRENDOUS!

If you can get away with a fixed query then do so.(One of the other examples)

If you have to cater for dynamic columns then let us...

Jeff Moden (1/1/2010)

---

Heh... I'm still concerned because this is for a bank and I apologize for even suggesting the use of STR(). Magoo... I owe you an apology because...

Thanks Jeff, yet another built in function I have never noticed!

The problem with all of this of course is that you are leaving yourself in the hands of SQL and...

DECLARE @input decimal(17,3)

DECLARE @decs tinyint

SELECT @input=1234.123,@decs=2

SELECT

CASE WHEN @decs = 2 THEN CAST(CAST(@input as decimal(17,2)) AS VARCHAR(19))

WHEN @decs = 1 THEN CAST(CAST(@input as decimal(17,1)) AS VARCHAR(19))

ELSE CAST(@input AS VARCHAR(19))

END

SELECT CAST(@input...

For the sake of it, a solution which copes with new "ColumnName" values by using dynamic sql (go ahead - pork chop me or whatever you do :-D)

If you don't...