Post reply

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

  • 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?

  • 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!

  • 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

     

  • May 5, 2006 at 2:14 am

    #636153

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply