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 |
|
Grofit
Starting Member
11 Posts |
 Posted - 2009-07-07 : 03:02:25
|
Hey,I have 2 databases that expose stored procs. One database contains user information and the other database contains content information.The problem i currently have (which i dont think was a problem in MySQL), the 1st stored proc that is run on one DB returns a list if IDs and values.. so something like:SELECT id,value FROM db1.table1 WHERE user_id = 1; Then when this data is brought back it is converted in the application into a string of comma delimited IDs. Then the other stored proc takes a comma delimited list of IDs and spits out content information... like shown below:SELECT name,description FROM db2.table2 WHERE id IN (@TheCommaDelimitedList); Now at the moment im passing in nvarchar for the comma delimited list, but it tries to convert it to a single value, so is there any way to do the above, a way to convert all the comma delimited numbers into correct numbers, or any way to get it to use them like numbers?If this was in 1 DB it would be alot easier:SELECT name,description FROM db.table2 WHERE id IN (SELECT id FROM db.table1 WHERE user_id = 1); Unfortunatly it has to be spread over 2 DBs and it cant really do cross DB queries... any help would be great! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-07 : 03:12:53
|
Why not:SELECT name,description FROM First_db..table2 WHERE id IN (SELECT id FROM Second_db..table1 WHERE user_id = 1)
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Grofit
Starting Member
11 Posts |
Posted - 2009-07-07 : 03:23:43
|
| As i said they are on 2 different DBs which may be on 2 completely different servers. May even be on different database providers... i.e one could be on MSSQL and one could be MYSQL, which is why its all done via consistant stored procs...Although i dont think any of that matters at the moment as its just over complicating the problem...I dont mind changing the SQL and doing it differently if needed, the only things that have made me take this route is:- I dont know how many ids will be needed, so i cant have X arguments and feed them in that way. Text would be the ideal option for me.- I ideally want to be able to do it as one query once i have all the IDs, otherwise i could just call a select for each of the ids, but would be very slow.I also have some other stored procs that use a very similar method that im going to have trouble with once i get to that section. So if i can fix the logic here will help me move on in other areas too. Is there any way to send an array or similar as an argument? as im assuming you are limited to just the normal number/text/blob types... |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-07 : 05:20:53
|
http://sommarskog.se/arrays-in-sql-2005.htmlGreetingsFred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Grofit
Starting Member
11 Posts |
Posted - 2009-07-07 : 08:09:08
|
| Thanks alot, a few options available there! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-07 : 08:23:28
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
SQL Stored Proc + IN(values)