Query results to list in SP

Question

Hey everyone,

I'm sure this is discussed somewhere, but after searching for a while I can't find it.

I'm building a stored procedure for SQL 2000 and I need to build a list of numbers that I can use in an IN function.

The query looks like this

SELECT DISTINCT type FROM dbo.table WHERE building = @currentBuilding

This will return typically between 1-5 results, I'm looking for a way to put that into an nvarchar or something.

I would like to dream there is an easy way to do this since it's in 1 column but I have a feeling I'm going to end up iterating over the results.

As a side, is nvarchar the right variable to use? is there a better way I could store these that would make IN work faster?

Thanks!

Answer 1

try something like:

declare @All nvarchar(1000)
SET @All=null

SELECT
    @All=ISNULL(@All+', ','')+CONVERT(varchar(10),d.value)
    FROM (SELECT 1 AS Value UNION SELECT 22 UNION SELECT 333 UNION SELECT 4444) d

PRINT @All

OUTPUT:

1, 22, 333, 4444

this is what you can use:

SELECT
    @All=ISNULL(@All+', ','')+CONVERT(varchar(10),d.type)
    FROM (SELECT DISTINCT type 
              FROM dbo.table 
              WHERE building = @currentBuilding
         ) d