stored procedure looping

SQL Server 2000
In the stored proc I want to get all the studentId's and concatenate them like
Select studentID from StudentTable
lets say 1,2,3,4,5 are studentIDs retrieved using the above query
I want to assign it to a variable  of string DataType  contactenated by "-"
  strStudentIds =  "1-2-3-4-5"
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
     declare @res varchar(8000)
      select @res = coalesce(@res + '-', '') +CAST(StudentID as varchar (10) )
      from StudentTable
      SELECT @Res

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Create a function as follows:

Create Function dbo.udf_GetStudentIDs (@DataType as integer)

Returns varchar(500)



Declare @StudentIDs varchar(500)

Select @StudentIDs = IsNull(@StudentIDs + '-', '') + CAST(StudentID as varchar(20))
From StudentTable
Where DataType = @DataType

Return @StudentIDs


You can then call it as follows:
Select DataType, dbo.udf_GetStudentIDs(DataType) Students
From StudentTable
Group by DataType
For performance considerations, I strongly suggest avoiding the use of User Defined Functions, if the function might ever be included as part of the where clause criteria.  The reason being that to see if the where clause criteria is true, every single row in the table must call the function to be evaluated.

So, for example, if you ever did this:

Select  DataType, dbo.udf_GetStudentIDs(DataType) Students
From StudentTable
where DataType = 'Transfer' and dbo.udf_GetStudentIDs(DataType)  LIKE '%BOB SMITH%'
Group by DataType, dbo.udf_GetStudentIDs(DataType)

That is going to have to run the udf_GetStudentIDs against every row in the StudentTable, even if the students with a DataType of 'Transfer' were a very small minority of the total population.  
dotnet0824Author Commented:
Thanks a lot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.