SQLTeam.com | Weblogs | Forums

Need help with TSQL in the View


#1

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


#2

The temporary table #Loaninfo will not exist for the view. So the view will not even know about the table.