converting SELECT output to string

I'm looking for some good hints and tips for reprogrammin an old VB module I just found.

Basically what it does, is receive an input parameter (an int), does a select [name row] from Names where Name_id = [input parameter] and turns this into a string if multiplenames appear.

E.g. result set: John, Josh, Jock turns it into string "John Josh Jock".

So its piece of cake creating a stored procedure selecting data on the base of an input parameter. Select X from Y where Z = @.input... the trick is, I don't know how to do arrays in TSQL as in VB.

In the VB edition I create an array, load the names into it, I do a count on how many row the select returns and then a simple for... next adding the names to the string.

Any good examples on how to do this in a sql-server stored proc?

Thanks,

Trin

P.S. This is what I have pieced together this far:

CREATE PROCEDURE findnames

@.number int

AS

DECLARE @.instrument varchar(50)
DECLARE @.tempinstt varchar(10)

DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

OPEN medlemcursor

FETCH NEXT FROM medlemcursor INTO @.tempinstt
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.instrument = @.instrument + @.tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @.tempinstt
END

CLOSE medlemcursor
DEALLOCATE medlemcursor

SELECT @.instrument
GO

Just doesn't seem to work, returns NULL, even though I've checked that the cursor SELECT statement actually returns data,--u have to intialize ur variable 'instrument' before appending other values.

CREATE PROCEDURE findnames

@.number int

AS

DECLARE @.instrument varchar(50)
DECLARE @.tempinstt varchar(10)
set instrument =''
DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

OPEN medlemcursor

FETCH NEXT FROM medlemcursor INTO @.tempinstt
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.instrument = @.instrument + @.tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @.tempinstt
END

CLOSE medlemcursor
DEALLOCATE medlemcursor

SELECT @.instrument
GO|||Yeap, got it... and I just added a small substring addendum to cut off the trailing dash.

Mind boggling NULLs are..

Is it possible to call such a procedure within a select statement?

E.g. SELECT number, (EXEC findsnames number), city FROM names|||Just dump your cursor:CREATE PROCEDURE findnames(@.number int)
AS

declare @.instrument varchar(500) --varchar(50) seemed awfully short...

SELECT coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
GO|||u cannot call procedure from select statement,
instead of procedure , create a function,call that function from select statment.

blindman,
Poster wants to append record into a string,and ur query will not do that job.
select @.instrument returns NULL.|||Corrected code:CREATE PROCEDURE findnames(@.number int)
AS

declare @.instrument varchar(500) --varchar(50) seemed awfully short...

SELECT @.instrument = coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
GO

Copy/paste this to try it out:create table #Names([MCPS Kode] varchar(50), number int)
insert into #Names([MCPS Kode], number)
select 'Joe', 1
UNION
select 'James', 1
UNION
select 'Frank', 2
UNION
select 'Janis', 1
UNION
select 'Freda', 2
UNION
select 'Jeff', 1
UNION
select 'Fred', 2
UNION
select 'Foster', 2
UNION
select 'Jodi', 1

---------------------
declare @.instrument varchar(500) --varchar(50) seemed awfully short...
declare @.number int
set @.number = 1

SELECT @.instrument = coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM #names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
---------------------

drop table #Names

Output:James-Janis-Jeff-Jodi-Joe

Yes, it can be converted into a function if the user wishes.