Passing an array of integers to a stored procedure
-
Hello, I currently have a stored procedure that I call multiple times with an ID to return a record. However, in my program, I sometimes need to return several records (for which I have all the IDs) at the same time. Is there a way I could pass an array of all IDs so I get all the necessary records with just one round-trip to the database? [EDIT -- Something like this would be ideal:
CREATE PROCEDURE Whatever ( @param int_array ) AS SELECT * FROM Table WHERE ID IN @param] Any help would be appreciated! Thanks! -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005 -- modified at 13:08 Thursday 22nd September, 2005
-
Hello, I currently have a stored procedure that I call multiple times with an ID to return a record. However, in my program, I sometimes need to return several records (for which I have all the IDs) at the same time. Is there a way I could pass an array of all IDs so I get all the necessary records with just one round-trip to the database? [EDIT -- Something like this would be ideal:
CREATE PROCEDURE Whatever ( @param int_array ) AS SELECT * FROM Table WHERE ID IN @param] Any help would be appreciated! Thanks! -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005 -- modified at 13:08 Thursday 22nd September, 2005
Use the IN operator and pass the integer ids as a comma separated string. Build the entire SQL statement in your application and execute the statement itself instead of calling a stored procedure. SELECT * FROM Table WHERE ID IN (param1,param2,...) Hope this helps... Regards, Orina DCosta http://orina.org
-
Use the IN operator and pass the integer ids as a comma separated string. Build the entire SQL statement in your application and execute the statement itself instead of calling a stored procedure. SELECT * FROM Table WHERE ID IN (param1,param2,...) Hope this helps... Regards, Orina DCosta http://orina.org
Yes, I have thought of that. But the stored procedure is not just a single
SELECT, so it is not as easy. Any other ideas? Thanks anyway, -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
-
Yes, I have thought of that. But the stored procedure is not just a single
SELECT, so it is not as easy. Any other ideas? Thanks anyway, -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
I prefer to use OpenXML rather than a comma delimited list. I'm not sure if that helps though - I suspect if you can't use one, you can't use the other ? OpenXML does translate nicely from XML to a table though, so it may be a better idea. Christian Graus - Microsoft MVP - C++
-
I prefer to use OpenXML rather than a comma delimited list. I'm not sure if that helps though - I suspect if you can't use one, you can't use the other ? OpenXML does translate nicely from XML to a table though, so it may be a better idea. Christian Graus - Microsoft MVP - C++
Hello Christian! Are you talking about the OPENXML[^] T-SQL Function? If yes, I think that could help. I could build a little XML table with all the IDs and get all of the at once. -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
-
Hello Christian! Are you talking about the OPENXML[^] T-SQL Function? If yes, I think that could help. I could build a little XML table with all the IDs and get all of the at once. -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
Yes, I am. That stuff totally rocks. Christian Graus - Microsoft MVP - C++