I need to return all the column in #LoanInfo table if the LoanId does not exist and EXIT it out.
If the record(s) exist continue with the UNION. I can't figerout how to check for record if not
exist in VIEW. Please see the desire output below. SQL 2012
Thank you in advance.
IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
DROP TABLE #LoanInfo
GO
CREATE TABLE #LoanInfo
(
LoanId VARCHAR(8) NULL,
Status VARCHAR(10) NULL
)
GO
INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')
GO
SELECT *
FROM #LoanInfo;
GO
DROP VIEW vTest
GO
CREATE VIEW vTest
(
LoanId
,Status
)
IF NOT EXISTS ( SELECT 1
FROM #LoanInfo)
BEGIN
SELECT *
FROM #LoanInfo;
RETURN (1)
END
-- IF the record exists
SELECT *
FROM t1
UNION
SELECT *
FROM t2
UNION
SELECT *
FROM t3
...
GO
SELECT *
FROM vTest
WHERE LoandId = 12345;
GO
--Desire output
LoanId Status
NULL NULL