Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dingels35
Starting Member
1 Post |
 Posted - 2004-11-01 : 21:30:00
|
I have a table called Users. Each user is linked to another user by the field user_parent. Given a user_id, I need to find the sum of all that user's decendants' points(user_curr_points), no matter how many levels down.In order to do the recursive step, I need a comma seperated list of user_id's to use in a "WHERE user_id IN ()" clause. The problem lies when trying to insert a comma seperated list into the SELECT statement below. There error generated when @this_uid = '1,2' is: "Syntax error converting the varchar value '1,2' to a column of data type int."I've tried putting the entire SELECT statement into a @query variable, and then using the EXEC command to run it, and this works, however it seems I can't get @uids back into my scope from the scope of the EXEC command.I can execute the recursion if I use the "=" instead of the "IN", however this causes way too many recursions and the time it takes to execute is not acceptable. Does anybody know of a better solution?
DECLARE @this_uid varchar(25)SET @this_uid ='1' <-- this one worksSET @this_uid ='1,2' <-- this one doesn't workDECLARE @uids varchar(100)SELECT @uids = COALESCE(@uids + ', ', '') + CAST(user_id AS varchar(5))FROM UsersWHERE user_parent IN (@this_uid)PRINT @uids |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-11-01 : 22:04:31
|
as far as the 'in' problem you can do:...where ','+@this_uid+',' like ('%,'+convert(varchar,user_Parent)+',%')...Also read through this for parent child type stuff:http://www.seventhnight.com/treestructs.aspCorey |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
recursive function