problem with stored procedure?
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+langN开发者_运维百科ame+','
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
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.
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.
精彩评论