User Defined Function 1

[lah3233]

Hi!

I've got a SQL Statement that looks like this:
SELECT project.strprojectid, strprojecttitle, dtDueDate, strProjectStatus, strConsultantName
FROM project, projectConsultant, Consultant
WHERE project.strProjectID = '9jjx1ujn4sa'
AND project.strProjectID = ProjectConsultant.strProjectID
AND projectconsultant.strConsultantID = consultant.strConsultantID

Basically there is more than one consultant per project...and what I would like to do is pull the first four fields and then somehow pull my consultants and list them together in a new temp field so I only have one row of data. Can anyone help me with this problem? I know I need to create some sort of UDF but am not sure how to go about this process.

Thanks!

 

[Mdavis123]

Hi Lah,

Instead of a UDF, I would use a SP that returns the result set I wanted. Here is some code snips

Declare @sAllConsultants varchar(1000),@sConsultantName varchar(100)

--Set up a cursor for the Project. If you want more than 1 project, you must create/write to a temp table first then select on your way out.

Declare PjtCsr Cursor for
SELECT project.strprojectid, strprojecttitle, dtDueDate, strProjectStatus
FROM project
WHERE project.strProjectID = '9jjx1ujn4sa'
Open PjtCsr
Fetch Next into @Var1,@Var2,@Var3,@Var4
While @@fetch_status=0
Begin
-- get each consultant for the project
-- and build one variable
Declare CnstntCsr Cursor for
Select sConsultantName from
projectConsultant, Consultant
Where clause...
Open CnstntCsr
fetch next from CnstntCsr into @sConsultantName
Begin
-- Build all consultant names
Set @sAllConsultantName= @sAllConsultantName+@sConsultantName +';'

End
Close CnstntCsr
Deallocate CnstntCsr
-- Remove the last ';'
Set @sAllConsultantName=left(@sAllConsultantName,len
(sAllConsultantName)-1)
-- Write to temp table
insert #TempTbl @Var1,@Var2,@Var3,@Var4,@sAllConsultantName

End
Close PjtCsr
deallocate PjtCsr

Select * from #TempTbl
Drop #TempTbl

Happy Hollidays
MikeD

 

[SleepingSand]

Since, Creation of CURSOR is a overhead to the system, it will hinder in performance.

Please check the following solution.

------------------------------------------------------

DECLARE @Consultant varchar(255)

SELECT @Consultant = COALESCE(@Consultant + ', ', ' ') + strConsultantName
FROM project, projectConsultant, Consultant
WHERE project.strProjectID = '9jjx1ujn4sa'
AND project.strProjectID = ProjectConsultant.strProjectID
AND projectconsultant.strConsultantID = consultant.strConsultantID

SELECT project.strprojectid, strprojecttitle, dtDueDate, strProjectStatus, @Consultant
FROM project, projectConsultant, Consultant
WHERE project.strProjectID = '9jjx1ujn4sa'

------------------------------------------------------

Thanx,
Sandeep

 

[SleepingSand]

Ooooooooops !!!! SORRY... Some change... in the second last line.
------------------------------------------------------

DECLARE @Consultant varchar(255)

SELECT @Consultant = COALESCE(@Consultant + ', ', ' ') + strConsultantName
FROM project, projectConsultant, Consultant
WHERE project.strProjectID = '9jjx1ujn4sa'
AND project.strProjectID = ProjectConsultant.strProjectID
AND projectconsultant.strConsultantID = consultant.strConsultantID

SELECT project.strprojectid, strprojecttitle, dtDueDate, strProjectStatus, @Consultant
FROM project
WHERE project.strProjectID = '9jjx1ujn4sa'

------------------------------------------------------

Thanx,
Sandeep

 

[mimi2]

Hello,
I tried your code.
i get this error and it points to the 2d select:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

do you know what this means?

 

[SleepingSand]

I m using SQL Server 2000, and it is working perfectly.
I have checked it by creating your tables and with some sample data.

 

[lah3233]

SleepingSand

Thanks for the help. The only issue I'm having is that there can be any number of consultants on a particular project. For example, in my database I have 3 consultants tied to a project, but it will only pull out two of them. How can I modify it to pull any given number of consultants?

Thanks again

 

[lah3233]

SleepingSand,

Nevermind - I forgot to change my projectID in one location. It's working flawlessly. Thanks for all your help!!

 

[mimi2]

Thanks a lot. It worked for me too.