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 |
|
aka_ankur
Starting Member
5 Posts |
 Posted - 2011-11-29 : 08:47:12
|
| I have created a report in which I have to select multiple comma separated values in parameter. When I put only query likeselect 'DB1' DBName, C1, C2, C3 from DB1.T1 inner join DB1.T2 on T1.C4 = T2.C4where 'DB1' in (@SelectedDatabase) UNION ALLselect 'DB2' DBName, C1, C2, C3 from DB2.T1 inner join DB2.T2 on T1.C4 = T2.C4where 'DB2' in (@SelectedDatabase) UNION ALLselect 'DB3' DBName, C1, C2, C3 from DB3.T1 inner join DB3.T2 on T1.C4 = T2.C4where 'DB3' in (@SelectedDatabase)Report is working fine for multiple values. But when I put this logic into stored proc. For single value it is working fine but for multiple value it is not working. Can some one please help me in this .logic for stored proc for accepting multiple comma separated values?Is this possible i can make my stored proc dynamic in a way so that it takes database name dynamically (for every new database I don't have to alter my proc each and every time). |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-11-29 : 10:37:55
|
There are multiple ways to do this - for example, parse the comma-separated values within the stored proc into a table and then join with that table etc. Another way would be to use the like clause. To test if that would work for you change the where clause to this:WHERE '%,'+ @SelectedDatabase + ',%' LIKE '%,'+'DB1'+',%' (and similar changes for the other where clauses) |
 |
|
|
|
|
|
|
|
Logic for Stored Proc for selecting multiple comma