is it possible to return 2 record sets from a single stored procedure?

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  May 4, 2006 at 3:19 am

  #172317

  Hi, I'm using SQL server 2000 and I've a question

  I'm after getting a stored procedure to return 2 record sets from 2 different databases where the data isn't actually in the same format, so I can use VB to do some kind of comparison. is this possible, if so how?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  May 4, 2006 at 8:38 am

  #635953

  there's a few issues here:

  a stored proc isn't going to return a set or records from a different database unless you explicitly refer to the database:

  select * from master.dbo.cities

  if itis froma  different server, the server has to be linked, and then you can call it via a 4 part name : linkedserver.master.dbo.cities

   

  if you are using vb6/ADO, then your script or procedure can return multiple recordsets. make sure you use SET NOCOUNT ON in your procedure:

  http://www.4guysfromrolla.com/webtech/083101-1.shtml

   

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  May 4, 2006 at 10:38 am

  #636028

  Sure.  Something like this will return 2 result sets

  create procedure test

  as

  set nocount on

  select * from master..sysobjects

  select * from master..syscolumns

  go

   

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  May 5, 2006 at 2:14 am

  #636153

  that's great, thanks people, I've actually managed to achieve what I need now