Looping through a select statement

Question

Post reply

Looping through a select statement

mcgraw_wv

Valued Member

Points: 62
More actions
October 22, 2001 at 1:13 pm

#157943

in a function,
I need to take the results of a select statement and make it one comma dilimeted string. Any help would be appreciated!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 1

http://www.sqlservercentral.com/columnists/sjones/tamestrings6.asp

Steve Jones

steve@dkranch.net

mcgraw_wv Valued Member Points: 62 More actions · Answer 2

I tried that and I get this error,

Error 444: select statements within A function cannot return data to a client.

or something like that.

is your example with a UDF or a Stored Procedure?

this is exactly what I have:

with the select statement returning multiple nvarchars.

CREATE function Get_Pop(@pop_group int)

Returns nvarchar(100)

Begin

declare @r varchar( 1000)

select @r = ''

select @r = rtrim(@r) + name ',' from SM_Servers,SM_group_Link where (SM_group_Link.group_name = @pop_group) and (SM_servers.ID = SM_Group_Link.group_item)

select substring( @r, 1, len( @r) - 1)

Return (@r)

End

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 3

The probklem is your last line. This is returning a value, not removing the last ,.

Is should read:

select @r = substring( ...

Steve Jones

steve@dkranch.net

mcgraw_wv Valued Member Points: 62 More actions · Answer 4

Doesnt the function have to return a value!

or inlined within another statement will it automatically do that, sort of like the function getdate()?

mcgraw_wv Valued Member Points: 62 More actions · Answer 5

CREATE function Get_Pop(@pop_group int)

Returns nvarchar(100)

BEGIN

declare @r nvarchar(1000)

select @r = ''

select @r = rtrim(@r) + name + "," from SM_Servers,SM_group_Link where (SM_group_Link.group_name = @pop_group) and (SM_servers.ID = SM_Group_Link.group_item)

select substring( @r, 1, len( @r) - 1)

END

I still get Error 444 with this

mcgraw_wv Valued Member Points: 62 More actions · Answer 6

Actually it worked I had the last line wrong

this is what I have

CREATE function Get_Pop(@pop_group int)

Returns nvarchar(1000)

BEGIN

declare @r nvarchar(1000)

select @r = ''

select @r = rtrim(@r) + type from SM_Servers,SM_group_Link where (SM_group_Link.group_name = @pop_group) and (SM_servers.ID = SM_Group_Link.group_item)

select @r = substring(@r, 1, len(@r) - 1)

RETURN(@r)

END

yet it returns a numeric value??????

mcgraw_wv Valued Member Points: 62 More actions · Answer 7

Damn Never mind, i screewed up again!

thank you so much for your help

you saved my ass!

nigelrivett SSCertifiable Points: 5472 More actions · Answer 8

to save the last substring you could make this

declare @r nvarchar(1000)

select @r = coalesce(@r+',','') + rtrim(type) from SM_Servers,SM_group_Link where (SM_group_Link.group_name = @pop_group) and (SM_servers.ID = SM_Group_Link.group_item)

Cursors never.
DTS - only when needed and never to control.