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"
dotnet0824Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
     declare @res varchar(8000)
      select @res = coalesce(@res + '-', '') +CAST(StudentID as varchar (10) )
      from StudentTable
      SELECT @Res
0
 
Anthony PerkinsConnect With a Mentor Commented:
Create a function as follows:

Create Function dbo.udf_GetStudentIDs (@DataType as integer)

Returns varchar(500)

As

BEGIN

Declare @StudentIDs varchar(500)

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

Return @StudentIDs

END


You can then call it as follows:
Select DataType, dbo.udf_GetStudentIDs(DataType) Students
From StudentTable
Group by DataType
0
 
mdouganConnect With a Mentor Commented:
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.  
0
 
dotnet0824Author Commented:
Thanks a lot
0
All Courses

From novice to tech pro — start learning today.