Database

Eddy Vluggen wrote:

Your users need access to an additional secret

DROP DATABASE insert_name_of_database_here

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');

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 @PROCEDURE
        SELECT * from #foobar
END

1)
--------------------
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

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

3)
------------------------------
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

EXEC LocalSchema.ExecuteCampaignOperation 'StarTrek', 'ShootToKill'

AccountNumber|AccountId|HomePhone|BusinessPhone|FirstName|LastName|AddressStuffs
1234567890	4	800-123-4567	800-231-4325	Captain	Kirk	123 Menagerie Lane.

if(@flag=1) then
  select * from TBL1
ELSE
  select * from TBL2

select * from TBL1 where @flag=1
union all
select * from TBL2 where @flag<>1
<pre>