| Author |
Topic |
|
sapator
Constraint Violating Yak Guru
462 Posts |
 Posted - 2010-12-01 : 20:42:49
|
| Hi.I'm researching this but all i can find is how to pass.P.E. exec someproc user1,user2,user3 ....etcWhat i want to do is to create comma separated array of users and then pass this array to a stored procedure that will take the users and do something.The second part if i'm not wrong can easily be done with using "in".But i cannot seem to find something for the first part.I assume i need to create a function for that?Any help would be appreciated.Thanks.P.S. I also thought to create a temp table with the users i want and then pass them.If this is more easy or better let me know.A question here is if the temp table values can be passed to "in" with something like:in(select userid from #temptable) |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-12-01 : 20:45:14
|
Temp table is way easier since the data will already be in a list form that way..don't use an array of comma separated values or you will have to use dynamic SQl or other means to then parse the list later.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-12-01 : 20:49:36
|
Aha.Just for the record i have something now:declare @csvstring varchar(1000)select @csvstring = coalesce(@csvstring + ',' + username , username) from users as usersmerged--select @csvstring Ok i'll have a temp table investigation then and post if i have problems. :) |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-12-01 : 20:53:23
|
| You may want to pass in 'user1,user2,user3,...,userN' to the sproc and then use ParseValues (found on this site) to turn the array into a table that you can then link on.JimEveryday I learn something that somebody else already knew |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-12-01 : 21:15:24
|
| Hey.The problems is that i don't want to pass manually the users one by one.Or do you mean something else?Thanks. |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2010-12-02 : 04:10:29
|
| Hi,Pls correct me if iam wrong.Why cant u construct a xml with user data and pass that to sp. I think its better and safe method than a comma separated value.Iam a slow walker but i never walk back |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-12-02 : 18:07:53
|
| I haven't used XML in sql so it's hard for me. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-12-02 : 19:04:13
|
| What environment is calling the stored procedure? Is it an application (.Net or other language), direct input (SSMS), reports (SSRS or other)?What is calling your stored procedure will allow for different options. - XML- Deleimited List- Table-valued Parameter- Multiple Paramters- Other? |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-12-02 : 22:38:05
|
| Hi.What i do is pass parameters to the asp membership database stored procedure "aspnet_UsersInRoles_RemoveUsersFromRoles" .I'm currently using the @csvstring method i mentioned above.Since i don't know if the database will blow if i change the nvarchar(4000) variable in "aspnet_UsersInRoles_RemoveUsersFromRoles" , i calculate the max char usernames,so 4000/maxcharusernames and i put a "select top" of this result.Am i on the right track?Thanks. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-12-04 : 02:12:19
|
| Well finished my job the way i described. |
|
|
|
|
create a comma seperated array and pass it.