Assistance with a Stored Proc working with the return results from another SP

Alaric_

Hi. I've been looking for specifics on this issue for the better part of the morning, but not seeing much help. Here's what I'm working with (background): I'm building a data-driven console application in C# that needs to schedule the execution of one of many reporting queries that I have encapsulated within stored procedures within a SS2008 db. The stored procs actually exist on a server that is referenced as a linked server to our production db. The references to the stored procs are stored in prod (i.e. Prod database table holds the name of a stored proc that exists on REPORTING server, REPORTING is in the list of Linked Servers of Prod) Let's say that I have 2 marketing campaigns that each have an "Outbound" operation and an "Inbound" operation. I have the campaigns stored in a reference table, I have the operations stored in a reference table, and I have the CampaignOperations stored in an associative entity that also contains the name of the specific stored procedure that holds the functionality of that campaign operation. (contrived example):

What I am trying to create is a stored procedure that acts as a broker/adapter for the campaign Operations. I want to be able to pass in "Campaign1","Outbound" to the public stored proc and have it dip into the CampaignOperations table, pick up C1OutboundFoo, execute it, and adapt the result set to a canonical representation for the calling client. So, all that said, I am having a bit of trouble figuring out how to get the results from one stored proc back into the broker for it to adapt to the canonical. I went the route of calling openquery, but I found from the documentation that you cannot pass a variable into OpenQuery...which is what led me back to the drawing board. Help??

"I need build Skynet. Plz send code"

Alaric_

Ok...so I'm still working through this scenario, and I've run across a possible lead to accomplish what I desire. If anyone has any feedback into the following approach, I would appreciate it. My application has a singular entry point into the database. For now, let's call it "ExecuteCampaignOperationLogic" that takes in 2 parameters: campaignName, OperationCode. Production Data Model:

create table Campaign
(
CampaignName varchar(50) Constraint PK_Campaign PRIMARY KEY,
CampaignMission varchar(max)
)
insert into Campaign(CampaignName, CampaignMission) values('StarTrek','5 year mission to
explore new worlds. To seek out new life and new civilization');

create table Operations
(
OperationCode VARCHAR(30) CONSTRAINT PK_Operations PRIMARY KEY
)
insert into Operations(OperationCode) values('EngageWarp');
insert into Operations(OperationCode) values('ComeInPeace');
insert into Operations(OperationCode) values('ShootToKill');

create table CampaignOperations
(
CampaignName VARCHAR(50),
OperationCode Varchar(30),
[Procedure] VarChar(max),
CONSTRAINT FK_Campaign_CampaignOperations FOREIGN KEY (CampaignName) REFERENCES Campaigns(CampaignName),
CONSTRAINT FK_Operations_CampaignOperations FOREIGN KEY (OperationCode) REFERENCES Operations(OperationCode),
CONSTRAINT PK_CampaignOperations PRIMARY KEY (CampaignName, OperationCode)
)
insert into CampaignOperations('StarTrek','EngageWarp', 'Reporting.StarTrekWarpAlgorithm');
insert into CampaignOperations('StarTrek','ComeInPeace', 'Reporting.StarTrekConTheLocals');
insert into CampaignOperations('StarTrek','ShootToKill', 'Reporting.StarTrekTakeMoneyAndWomens');

So...I want "ExecuteCampaignOperationLogic" to take the CampaignName + OperationCode into the CampaignOperations table, pull the appropriate Procedure name, execute it, receive the returned resultset, adapt the results to a canonical representation of the Campaign data, and toss back to the calling client, which will be responsible for mapping this data to a Business Object. Here's what I'm trying:

CREATE PROCEDURE ExecuteCampaignOperationLogic
(
@CampaignName varchar(50),
@OperationCode varchar(10)
)
AS
BEGIN
DECLARE @PROCEDURE varchar(100)

SELECT	@PROCEDURE = \[CampaignOperations\].\[Procedure\]
FROM \[CampaignOperations\]
	WHERE CampaignName = @CampaignName
	  and OperationCode = @OperationCode

CREATE TABLE #foobar
(
	foo varchar(10),
	bar varchar(10)
)
INSERT INTO #foobar (foo, bar) exec @PROCEDUR

Alaric_

Ok....so I got everything wired up to test out the above technique and I'm getting "an INSERT EXEC statement cannot be nested" when attempting to pass my parameters into an EXEC of the broker. Any ideas other than changing the inner stored proc to be a table-valued function?

"I need build Skynet. Plz send code"

JOAT MON

I don't have any experience working with linked servers, but this this page[^] seems to compare the issues you are contemplating pretty well.

Jack of all trades ~ Master of none.

Alaric_

Thank you very much for your assistance. The link contained much useful information, but to no avail. I'll be posting a follow up shortly.

"I need build Skynet. Plz send code"

Alaric_

Su-weet! Hurdle jumped. Below is a mock-up of the code that works for me Manifest: 1) Table-Valued function created in Reporting that will encapsulate "campaign operation" specific querying logic. 2) Row inserted into CampaignOperations table with a pointer to (1) 3) Broker/Adapter procedure created that accepts a CampaignName and OperationCode ...found out along the way that an INSERT-EXEC stored proc cannot call an INSERT-EXEC stored proc and that you cannot execute remote table-valued functions. Note that [RemoteSchema] is a schema located in a remote database called "Reporting." [LocalSchema] is the local schema that my application has access to. "Reporting" is a linked server of my local database.

USE [REPORTING]
GO

10:11:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [RemoteSchema].[TakeAllTheMoneyAndWomens]()
RETURNS @Solicitations TABLE
(
AccountId varchar(20) PRIMARY KEY NOT NULL,
AccountNumber varchar(20) NOT NULL,
HomePhone varchar(12) NULL,
BusinessPhone varchar(12) NULL,
FirstName varchar(70) NULL,
LastName varchar(70) NULL,
AddressStuffs varchar(max) NULL
)
AS
BEGIN
INSERT @Solicitations
SELECT '5','666321234','111-456-7890', '800-123-4567','Captain','Kirk','123 Menagerie Lane';
RETURN;
END;
GO

insert into LocalSchema.CampaignOperations values('StarTrek','ShootToKill','REPORTING.RemoteSchema.TakeAllTheMoneyAndWomens()')

USE [AppData]
GO
if(EXISTS(SELECT * FROM Information_Schema.ROUTINES where ROUTINE_SCHEMA = 'LocalSchema' and ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_NAME = 'ExecuteCampaignOperation'))
drop PROCEDURE [LocalSchema].[ExecuteCampaignOperation]
10:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [LocalSchema].[ExecuteCampaignOperation]
(
@CampaignName varchar(50),
@OperationCode varchar(10)
)
AS
BEGIN
DECLARE @PROCEDURE nvarchar(100);
DECLARE @SQL nvarchar(max);

SELECT	@PROCEDURE = Operations.\[Algorithm\]
FROM \[LocalSchema\].\[CampaignOperations\] \[Operations\]
WHERE CampaignName = @CampaignName
  and OperationCode = @OperationCode; 
SET @SQL = N'SELECT AccountNumber, AccountId, HomePhone, BusinessPhone, FirstName, LastName, AddressStuffs 
FROM OpenQuery(REPORTING, ''SELECT \* FROM ' + @PROCEDURE + ''')';
EXECUTE sp\_executesql @SQL;
RETURN;

END

GO

JOAT MON

Congratulations! :-D

Jack of all trades ~ Master of none.