Hi- This is for any SQL guru's out there. I am working with a Participant query. I need to return some attribute information, but don't want duplicates if there are multiple instances of a single attribute. The only way I could think of to do this is to create a User Defined Function. I've not done much of this before and I'm sure I'm missing something simple. This is what I've got so far: CREATE FUNCTION dbo.MultipleAttributes (@participantid integer, @attributetype integer) RETURNS nvarchar(255) BEGIN declare @text nvarchar(255) select @text = Line1.TEXT + char(10) + Line2.TEXT + char(10)+ Line3.TEXT FROM (SELECT parentid, TEXT, ATTRIBUTETYPESID FROM RE7.dbo.ParticipantAttributes WHERE (PARENTID = @participantid AND sequence = 1 AND ATTRIBUTETYPESID = @attributetype)) Line1 LEFT OUTER JOIN (SELECT parentid, TEXT, ATTRIBUTETYPESID FROM RE7.dbo.ParticipantAttributes WHERE (PARENTID = @participantid AND sequence = 2 AND ATTRIBUTETYPESID = @attributetype)) Line2 ON Line1.parentid = Line2.parentid RIGHT OUTER JOIN (SELECT PARENTID, TEXT, ATTRIBUTETYPESID FROM RE7.dbo.ParticipantAttributes WHERE (PARENTID = @participantid AND sequence = 3 AND ATTRIBUTETYPESID = @attributetype)) Line3 ON Line1.parentid = Line3.PARENTID RIGHT OUTER JOIN RE7.dbo.Participants ON Line1.PARENTID = RE7.dbo.Participants.ID WHERE (RE7.dbo.Participants.ID = @participantid) return(@text) end I want to be able to run something like: SELECT ID, dbo.MultipleAttributes(ID, 27) AS AttributeText FROM RE7.dbo.Participants WHERE (ID = 24821) Which would merge the text fields from all attributes of type 27 for participant 24821 into a single field. If I hardcode 27 in the function, I get what I'm looking for. However, I want to make this a multipurpose function and when I feed the attribute type by parameter, I get blanks. Can anyone help? Thanks! Mitch Gibbs Director, IS Advocate Charitable Foundation

Mitch, There is probably a way to make this work using the approach you've got, but I think you might find it easier to get this accomplished by using the FETCH command through a cursor. SQL Server books has got some examples if you go to the help page for FETCH. In a nutshell, you'd create a cursor with all the attributes of the type you specified in it for the participant and then loop through them building a result string as you go. Good luck! Frank Frank Dean Director, Gift Processing The Children's Hospital Foundation

Hi Mitchell, After the code walkthrough, I think that you would like to make generalized function which will return a string by concatenating maximum of Three instances of Participant attribute information. Here are the required queries if I am getting your requirements correct! Execute the below mentioned SQL in Query Analyzer to create function “MultipleAttributes” in raisers edge database by selecting the proper database name from the top pane. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MultipleAttributes]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[MultipleAttributes] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.MultipleAttributes (@participantid integer, @attributetype integer) RETURNS nvarchar(255) BEGIN declare @text nvarchar(255) set @text = (Select isnull(TEXT1,'') + ' ' + isnull(text2,'') + ' ' + isnull(text3,'') as Txt FROM (SELECT parentid, ATTRIBUTETYPESID FROM ParticipantAttributes WHERE (PARENTID = @participantid AND sequence = 1 AND ATTRIBUTETYPESID = @attributetype)) A LEFT OUTER JOIN (SELECT parentid, TEXT as text1, ATTRIBUTETYPESID FROM ParticipantAttributes WHERE (PARENTID = @participantid AND sequence = 1 AND ATTRIBUTETYPESID = @attributetype)) Line1 ON A.parentid = Line1.parentid LEFT OUTER JOIN (SELECT parentid, TEXT as text2, ATTRIBUTETYPESID FROM ParticipantAttributes WHERE (PARENTID = @participantid AND sequence = 2 AND ATTRIBUTETYPESID = @attributetype)) Line2 ON Line1.parentid = Line2.parentid LEFT OUTER JOIN (SELECT PARENTID, TEXT as text3, ATTRIBUTETYPESID FROM ParticipantAttributes WHERE (PARENTID = @participantid AND sequence = 3 AND ATTRIBUTETYPESID = @attributetype)) Line3 ON Line1.parentid = Line3.PARENTID LEFT OUTER JOIN Participants ON a.PARENTID = Participants.ID where a.parentID = @participantid) return (@text) end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO After executing the SQL try to run the below mentioned SQL SELECT     ID, dbo.MultipleAttributes(ID, 27) AS AttributeText FROM         RE7.dbo.Participants WHERE     (ID = 24821) This will return a single Record with attribute information in attributetext column Please let me know If I am correct in my understanding of your problem. HTH Sincerely, Dinesh --------------------------------------- Greytrix Blackbaud Development Partner [Email Removed] ---------------------------------------

Dinesh- That is exactly what I was looking to do and it works perfectly. Thanks very much. Thanks also to Frank. I think your suggestion is probably a better way to go long term, it's a little more open than what I originally planned, and I need to learn cursors at some point anyway. I'll go ahead with Dinesh's code and work on your suggestion when I get the opportunity. You guys rock. Mitch