problem with stored procedure?

Question

This is my main table structure:

The expertise column is a many to many relationship with another table that has a list of available languages. I wanted to have a flattened structure to display all the languages that a person expertise's, so I wrote a stored procedure that stringifies those multiple expertises to fit for each user.

When I called this sp from my wpf application, its not showing the values. I tried the 'preview data' from object browser which showed only one row of my table.

(Expected result:

What is the problem with my approach?

my Sp:

create procedure myView as
Begin
DECLARE @count INT,@finCount INT,@result varchar(50)
SET @result =' '
SET @count = 1
SELECT @finCount=COUNT(*) FROM usersProfile 
WHILE (@count <= @finCount )
 BEGIN
   SELECT @result=@result+langName+','
   FROM expertises
   INNER JOIN ED_UPD_MERGE on expertises.id=ED_UPD_MERGE.idfrmED
   INNER JOIN usersprofile on ED_UPD_MERGE.idfrmUPD=usersprofile.expertiseid
   WHERE  usersprofile.id =@count

   SELECT usersProfile.id,usersProfile.fullname,usersProfile.screename  ,usersProfile.age ,usersProfile.address ,usersProfile.emailid1 ,usersProfile.emailid2 ,usersProfile.isActive ,usersProfile.entryCreated ,usersProfile.entryModified ,usersProfile.experience ,roles.rolesName,@result as Expertise
   FROM usersProfile        
   JOIN roles
   ON usersProfile.roleid =roles.id 
   WHERE  usersprofile.id =@count
   SET @result= ''
   SET @count = (@count + 1) 
  END
 End

Answer 1

Could you post the WPF code?

---

As a sidenote:

It would be a far better design to have a table Expertise and then map that field to the expertise IDs. So basically:

Table:

• ExpertiseID
• ExpertiseDescription

Then in your field that you're currently using for Expertise, you'll create a bridging table and create a relationship the new expertise table.

Answer 2

Does the langName column allow NULL values? If you concatenate at least one NULL to the @result string, you'll get a NULL result. If NULLS are allowed in the langName column, and assuming you want to ignore rows with langName you could change:

SELECT @result=@result+langName+','

to

SELECT @result=@result+ ISNULL(langName+',', '')

This will add an empty string to the end of @result if langName is null; otherwise, langName will be appended to the string.