SQL Scripts from Synergy - example of table variable

--LET SAY I NEED THE DISTINCT LIST OF KEY VALUES FROM CONTRACT MASTER AND HISTORY
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00500
UNION
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00501
--I WILL BE DOING DIFFERENT THINGS WITH THE DATA RETURNED
--RATHER THAN RUN THE ABOVE QUERY MULTIPLE TIMES
--I CAN USE A TABLE VARIABLE
DECLARE @CONTRACTS AS TABLE(
 CUSTNMBR CHAR(15),
 ADRSCODE CHAR(15),
 Contract_Number CHAR(11),
 WSCONTSQ INT
 ,UNIQUE(CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ)  --THIS TRICKS SQL INTO GIVING ME AN INDEX
 )
INSERT INTO @CONTRACTS
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00500
UNION
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00501
--NOW I CAN USE MY TABLE VARIABLE PRETTY MUCH LIKE A NORMAL TABLE
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM @CONTRACTS
SELECT *
FROM dbo.SV00582 S
 INNER JOIN @CONTRACTS C
  ON S.CUSTNMBR=C.CUSTNMBR
   AND S.ADRSCODE=C.ADRSCODE
   AND S.Contract_Number=C.Contract_Number
   AND S.WSCONTSQ=C.WSCONTSQ


Did this help you?  If so, please leave a comment!