Ok, here is a stored procedure that our old DBA create, it uses data in another database but it does not implicitly reference it in the stored procedure. The name of the other database is "IMS". My question is there a way to configure a database to also add in the schema from another database so you don't have to use the "use database" or the fully qualified schema name?
Here is the stored procedure, I never see a reference to the "IMS" database, which is where the data is coming from.
Here is the stored procedure, I never see a reference to the "IMS" database, which is where the data is coming from.
Code:
ALTER PROCEDURE [MarketForecaster].[IMS_IMSDrugSelect]
@VersionID INT
, @ATC4 VARCHAR(32)
, @CombinedMolecule VARCHAR(768)
, @Form1 VARCHAR(64)
AS
BEGIN
DECLARE @BaseYear INT
DECLARE @BeginDate SMALLDATETIME
DECLARE @EndDate SMALLDATETIME
DECLARE @DrugID TABLE
(
IMSDrugID UNIQUEIDENTIFIER
)
DECLARE @ExManSales TABLE
(
IMSDrugID UNIQUEIDENTIFIER
, ExmanSales DECIMAL(18,5)
)
DECLARE @UnitSales TABLE
(
IMSDrugID UNIQUEIDENTIFIER
, UnitSales DECIMAL(18,5)
, SalesType VARCHAR(32)
--, UnitDescriptor VARCHAR(150)
)
SET @BaseYear =
(
SELECT
MIN(YEAR(dbo.ForecastPeriod.PeriodStartDate))
FROM
dbo.ForecastPeriod
WHERE
dbo.ForecastPeriod.VersionID = @VersionID
)
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- Currently, this query should not return any results if the BaseYear
---- of the forecast cannot be determined.
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
IF @BaseYear IS NOT NULL
BEGIN
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- These dates are used for the BETWEEN operator when
---- returning ExManSales and UnitSales for the BaseYear of a drug
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
SET @BeginDate = CONVERT(SMALLDATETIME, '01/01/' + CAST(@BaseYear AS VARCHAR(4)), 101)
SET @EndDate = CONVERT(SMALLDATETIME, '12/31/' + CAST(@BaseYear AS VARCHAR(4)), 101)
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- These IDs are used to filter the queries used to
---- aggregate ExManSales and UnitSales.
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
@DrugID
SELECT
dbo.IMSDrug.IMSDrugID
FROM
dbo.IMSDrug
WHERE
dbo.IMSDrug.ATC4 = @ATC4
AND dbo.IMSDrug.CombinedMolecule = @CombinedMolecule
AND dbo.IMSDrug.Form1 = @Form1
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- Retrieves the ExManSales for the drugs
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
@ExManSales
SELECT
dbo.IMSDrugSales.IMSDrugID
, SUM(dbo.IMSDrugSales.SalesValue) AS ExManSales
FROM
dbo.IMSDrugSales
WHERE
--Ex-Man Sales
dbo.IMSDrugSales.IMSDrugID IN
(
SELECT
D.IMSDrugID
FROM
@DrugID D
)
AND dbo.IMSDrugSales.SalesTypeID = 1
AND dbo.IMSDrugSales.SalesDate BETWEEN @BeginDate AND @EndDate
GROUP BY
dbo.IMSDrugSales.IMSDrugID
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- Retrieves the UnitSales and UnitType for the drugs for USA
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
@UnitSales
SELECT
dbo.IMSDrugSales.IMSDrugID
, SUM(dbo.IMSDrugSales.SalesValue) AS UnitSales
, dbo.IMSDrugSalesType.SalesType AS UnitType
FROM
dbo.IMSDrugSales
INNER JOIN
dbo.IMSDrugSalesType
ON
dbo.IMSDrugSales.SalesTypeID = dbo.IMSDrugSalesType.SalesTypeID
WHERE
--Unit Sales
dbo.IMSDrugSales.IMSDrugID IN
(
SELECT
D.IMSDrugID
FROM
@DrugID D
)
AND dbo.IMSDrugSales.SalesTypeID > 1
AND dbo.IMSDrugSales.SalesDate BETWEEN @BeginDate AND @EndDate
GROUP BY
dbo.IMSDrugSales.IMSDrugID
, dbo.IMSDrugSalesType.SalesType
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- Retrieves the UnitSales and UnitType for the drugs for Rest of World
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
INSERT INTO
@UnitSales
SELECT
dbo.IMSDrugSales.IMSDrugID
, SUM(dbo.IMSDrugSales.SalesValue) AS UnitSales
, dbo.IMSDrugSalesType.SalesType AS UnitType
FROM
dbo.IMSDrugSales
INNER JOIN
dbo.IMSDrugSalesType
ON
dbo.IMSDrugSales.SalesTypeID = dbo.IMSDrugSalesType.SalesTypeID
WHERE
--Unit Sales
dbo.IMSDrugSales.IMSDrugID IN
(
SELECT
D.IMSDrugID
FROM
@DrugID D
)
AND dbo.IMSDrugSales.SalesTypeID > 1
AND dbo.IMSDrugSales.SalesDate BETWEEN @BeginDate AND @EndDate
GROUP BY
dbo.IMSDrugSales.IMSDrugID
, dbo.IMSDrugSalesType.SalesType
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
---- Returns the actual search results.
----
---- If ExMan sales records do not exist for a given drug,
---- the value returned is 0.
----
---- If Unit sales records do not exist for a given drug, the
---- sales return is 0, and the unit type returned is 'N/A'
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
SELECT
--dbo.IMSDrug.IMSDrugID
dbo.IMSDrug.Country
, dbo.IMSDrug.ATC4
, dbo.IMSDrug.CombinedMolecule
, dbo.IMSDrug.Form1
, dbo.IMSDrug.Company
, dbo.IMSDrug.Product
, dbo.IMSDrug.BrandGeneric
, dbo.IMSDrug.LaunchDate
, dbo.IMSDrug.Form3
, dbo.IMSDrug.Strength
, dbo.IMSDrug.Package
, ISNULL(E.ExmanSales, 0) AS ExManSales
, ISNULL(U.UnitSales, 0) AS UnitSales
, ISNULL(U.SalesType, 'N/A') AS UnitType
FROM
dbo.IMSDrug
LEFT OUTER JOIN
@ExManSales E
ON
dbo.IMSDrug.IMSDrugID = E.IMSDrugID
LEFT OUTER JOIN
@UnitSales U
ON
dbo.IMSDrug.IMSDrugID = U.IMSDrugID
WHERE
dbo.IMSDrug.IMSDrugID IN
(
SELECT
D.IMSDrugID
FROM
@DrugID D
)
END
RETURN;
END