Is there a Better Way for this

Question

Post reply

Is there a Better Way for this

Douglas Bentley

SSC-Addicted

Points: 435
More actions
November 2, 2004 at 8:26 pm

#60481

I have a client that is wanting to pull data from a select as {Value,Value,....}
There are two tables involved. Company and Client.
Client has a foriegn key back to company. They want to pull all the clients where company = X in the format of {Client, Client, Client....}
So I created a udf to call and hopefully someone will have a better mouse trap.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RO_TEST]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[RO_TEST]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Function dbo.RO_TEST (@COMP VARCHAR(50))
Returns Varchar(1000)
As
Begin
DECLARE @CLIENT AS VARCHAR(50)
Declare @finallist As varchar(1000)
Declare list_cursor Cursor
For SELECT C.[CLIENT]
  FROM [_SMDBA_].[_CUSTOMER_] C, [_SMDBA_].[_COMPANY_] CO
  Where C.COMPANY=CO.[SEQUENCE]
  AND CO.CODE=@COMP
Open list_cursor
Fetch Next From list_cursor
into @CLIENT
Select @finallist =''
While @@Fetch_Status = 0
Begin
Select @finallist = @finallist+', '+ @CLIENT
Fetch Next From list_cursor
into @CLIENT
End
Close list_cursor
Deallocate list_cursor
return(@finallist)
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--SELECT [dbo].[RO_TEST]('US-BAKERSF-PACI')

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

This should work.

Create Function dbo.RO_TEST (@COMP VARCHAR(50))

Returns Varchar(1000)

As

Begin

Declare @finallist As varchar(1000)

SELECT @finallist = @finallist + C.[CLIENT] + ', '

FROM [_SMDBA_].[_CUSTOMER_] C, [_SMDBA_].[_COMPANY_] CO

Where C.COMPANY=CO.[SEQUENCE]

AND CO.CODE=@COMP

Select @finallist=LEFT(@finallist,LEN(@finallist)-2)

return(@finallist)

End

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Douglas Bentley SSC-Addicted Points: 435 More actions · Answer 2

Gila,

Thanks for posting, unfortunately when I tried this the results were null.

So I verified that I had selected correct data and compared results, yep it was correct. Which means this did not work.

Huge thanks for the try! I will see if I can get it to work using this format.

Doug

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Because I forgot to initialise the string first and uninitialised strings are null. Add this before the select and it should work

Select @finallist =''

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Douglas Bentley SSC-Addicted Points: 435 More actions · Answer 4

SAWEET!!!

Thanks, I knew there was a better mouse trap.

Doug