Procedure wrapper needs 2nd Resultset?

[Jonathan Beam]

I'm trying to create a wrapper procedure. The procedure I'm calling
returns 2 resultsets. I really only need the second, but I'm not sure
how to get the second resultset. Is there something like ADO's
Resultset.NextRecordset for Transaction SQL?


CREATE PROCEDURE dbo.NAMissionsSearchNameWrapper @Search varchar,
@SortByName INT
AS
CREATE TABLE #NaMissionsSearchNameTemp(
AccountNumber VARCHAR(12) NOT NULL,
AccountName VARCHAR(512) NULL,
Address1 VARCHAR(255) NULL,
Address2 VARCHAR(255) NULL,
Address3 VARCHAR(255) NULL,
Address4 VARCHAR(255) NULL,
PPL VARCHAR(255) NULL, --city
ADM2 VARCHAR(255) NULL, --whats this?
ADM1 VARCHAR(255) NULL, --state
FIPSCountryCode VARCHAR(50) NULL,
PostalCode VARCHAR(255) NULL,
ExportAddressAsIs BIT(2) NULL,
Name1 VARCHAR(255) NULL,
Name2 VARCHAR(255) NULL,
Name3 VARCHAR(255) NULL,
Name4 VARCHAR(255) NULL,
LastName VARCHAR(255) NULL,
FamiliarName VARCHAR(255) NULL,
Title VARCHAR(255) NULL,
Suffix VARCHAR(255) NULL,
FirstName VARCHAR(255) NULL,
MiddleInitial VARCHAR(255) NULL,
ProperName VARCHAR(512) NULL,
GenderDesignation VARCHAR(10) NULL,
MainframeAddressNumber VARCHAR(10) NULL,
PostalAddressGUID VARCHAR(50) NULL,
GUID UNIQUEIDENTIFIER NULL)

INSERT INTO #NaMissionsSearchNameTemp
SELECT NA.NARepository.dbo.usp_DP_NARSearchNameAddress
@@Search,
@SortByName
..
..
..

 

[Ken Schaefer]

Hi,

If you want to suppress the "XX records affected" resultset, then use SET
NOCOUNT ON

Cheers
Ken

: I'm trying to create a wrapper procedure. The procedure I'm calling
: returns 2 resultsets. I really only need the second, but I'm not sure
: how to get the second resultset. Is there something like ADO's
: Resultset.NextRecordset for Transaction SQL?
:
:
: CREATE PROCEDURE dbo.NAMissionsSearchNameWrapper @Search varchar,
: @SortByName INT
: AS
: CREATE TABLE #NaMissionsSearchNameTemp(
: AccountNumber VARCHAR(12) NOT NULL,
: AccountName VARCHAR(512) NULL,
: Address1 VARCHAR(255) NULL,
: Address2 VARCHAR(255) NULL,
: Address3 VARCHAR(255) NULL,
: Address4 VARCHAR(255) NULL,
: PPL VARCHAR(255) NULL, --city
: ADM2 VARCHAR(255) NULL, --whats this?
: ADM1 VARCHAR(255) NULL, --state
: FIPSCountryCode VARCHAR(50) NULL,
: PostalCode VARCHAR(255) NULL,
: ExportAddressAsIs BIT(2) NULL,
: Name1 VARCHAR(255) NULL,
: Name2 VARCHAR(255) NULL,
: Name3 VARCHAR(255) NULL,
: Name4 VARCHAR(255) NULL,
: LastName VARCHAR(255) NULL,
: FamiliarName VARCHAR(255) NULL,
: Title VARCHAR(255) NULL,
: Suffix VARCHAR(255) NULL,
: FirstName VARCHAR(255) NULL,
: MiddleInitial VARCHAR(255) NULL,
: ProperName VARCHAR(512) NULL,
: GenderDesignation VARCHAR(10) NULL,
: MainframeAddressNumber VARCHAR(10) NULL,
: PostalAddressGUID VARCHAR(50) NULL,
: GUID UNIQUEIDENTIFIER NULL)
:
: INSERT INTO #NaMissionsSearchNameTemp
: SELECT NA.NARepository.dbo.usp_DP_NARSearchNameAddress
: @@Search,
: @SortByName
: .
: .
: .