Is there a Better Way for this

• Douglas Bentley

  Old Hand

  Points: 355

  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')

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  November 3, 2004 at 12:30 am

  #528720

  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

  Old Hand

  Points: 355

  More actions

  November 3, 2004 at 5:18 am

  #528754

  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: 1004494

  More actions

  November 3, 2004 at 5:30 am

  #528756

  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

  Old Hand

  Points: 355

  More actions

  November 3, 2004 at 5:51 am

  #528759

  SAWEET!!!

  Thanks, I knew there was a better mouse trap.

  Doug