Microsoft SQL code example to pull Syteline contacts from all contact locations.
USE [testsite_live_App]GO
/****** Object: StoredProcedure [MYSCHEMA].[_chad_outlook_addin_autofind] Script Date: 06/25/2013 19:10:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MYSCHEMA].[_chad_outlook_addin_autofind]
@EmailAddress nvarchar(60)
,@Site nvarchar(10) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Company nvarchar(60)
DECLARE @Addr1 nvarchar(50)
DECLARE @City nvarchar(30)
DECLARE @State nvarchar(5)
DECLARE @Country nvarchar(30)
DECLARE @PCI_Num nvarchar(60)
DECLARE @Seq nvarchar(60)
CREATE TABLE #search_results(
siteid nvarchar(60)
,contact_type nvarchar(60)
,company_name nvarchar(60)
,address1 nvarchar(50)
,city nvarchar(30)
,state nvarchar(5)
,country nvarchar(30)
,pci_num nvarchar(7)
,seq int)
IF @Site IS NULL OR @Site = 'testsite'
BEGIN
--**************************************
--************testsite********************
--**************************************
--Customer ShipTo External Email address
DECLARE Cust_ShipTo_testsite_Cursor CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite_Live_App.dbo.custaddr AS T1
INNER JOIN testsite_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.external_email_addr = @EmailAddress
OPEN Cust_ShipTo_testsite_Cursor
FETCH NEXT FROM Cust_ShipTo_testsite_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_ShipTo_testsite_Cursor
--Customer ShipTo Internal Email address
DECLARE Cust_ShipTo_testsite_Cursor2 CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite_Live_App.dbo.custaddr AS T1
INNER JOIN testsite_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.internal_email_addr = @EmailAddress
OPEN Cust_ShipTo_testsite_Cursor2
FETCH NEXT FROM Cust_ShipTo_testsite_Cursor2
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite_Cursor2
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_ShipTo_testsite_Cursor2
--Customer ShipTo ShipTo Email address
DECLARE Cust_ShipTo_testsite_Cursor3 CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite_Live_App.dbo.custaddr AS T1
INNER JOIN testsite_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.ship_to_email = @EmailAddress
OPEN Cust_ShipTo_testsite_Cursor3
FETCH NEXT FROM Cust_ShipTo_testsite_Cursor3
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite_Cursor3
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_ShipTo_testsite_Cursor3
--Customer ShipTo Bill To Email address
DECLARE Cust_ShipTo_testsite_Cursor4 CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite_Live_App.dbo.custaddr AS T1
INNER JOIN testsite_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.bill_to_email = @EmailAddress
OPEN Cust_ShipTo_testsite_Cursor4
FETCH NEXT FROM Cust_ShipTo_testsite_Cursor4
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite_Cursor4
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
--Prospects Email address
--DECLARE Prospects_testsite_Cursor CURSOR LOCAL STATIC FOR
-- SELECT T3.company
-- ,T3.addr##1
-- ,T3.city
-- ,T3.state
-- ,T3.country
-- ,T2.prospect_id
-- FROM testsite_Live_App.dbo.contact AS T1
-- INNER JOIN testsite_Live_App.dbo.prospect_contact AS T2
-- ON T1.contact_id = T2.contact_id
-- INNER JOIN testsite_Live_App.dbo.prospect AS T3
-- ON T2.prospect_id = T3.prospect_id
-- WHERE T1.email = @EmailAddress
--OPEN Prospects_testsite_Cursor
--FETCH NEXT FROM Prospects_testsite_Cursor
--INTO @Company
-- ,@Addr1
-- ,@City
-- ,@State
-- ,@Country
-- ,@PCI_Num;
--WHILE @@FETCH_STATUS = 0
--BEGIN
-- INSERT INTO #search_results (
-- siteid
-- ,contact_type
-- ,company_name
-- ,address1
-- ,city
-- ,state
-- ,country
-- ,pci_num
-- ,seq)
-- VALUES( 'testsite'
-- ,'Prospect'
-- ,@Company
-- ,@Addr1
-- ,@City
-- ,@State
-- ,@Country
-- ,@PCI_Num
-- ,'0')
-- FETCH NEXT FROM Prospects_testsite_Cursor
-- INTO @Company
-- ,@Addr1
-- ,@City
-- ,@State
-- ,@Country
-- ,@PCI_Num;
--END
--CLOSE Prospects_testsite_Cursor
--Customers ShipTo SC Email address
DECLARE Cust_SC_testsite_Cursor CURSOR LOCAL STATIC FOR
SELECT T3.name
,T3.addr##1
,T3.city
,T3.state
,T3.country
,T3.cust_num
,T3.cust_seq
FROM testsite_Live_App.dbo.contact AS T1
INNER JOIN testsite_Live_App.dbo.customer_contact AS T2
ON T1.contact_id = T2.contact_id
INNER JOIN testsite_Live_App.dbo.custaddr AS T3
ON T2.cust_num = T3.cust_num
AND T2.cust_seq = T3.cust_seq
INNER JOIN testsite_Live_App.dbo.customer AS T4
ON T3.cust_num = T4.cust_num
AND T3.cust_seq = T4.cust_seq
WHERE T1.email = @EmailAddress
OPEN Cust_SC_testsite_Cursor
FETCH NEXT FROM Cust_SC_testsite_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_SC_testsite_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_SC_testsite_Cursor
--Service Contacts Email address
DECLARE Service_SC_testsite_Cursor CURSOR LOCAL STATIC FOR
SELECT T2.name
,T2.addr##1
,T2.city
,T2.state
,T2.country
,T2.cust_num
,T2.cust_seq
FROM testsite_Live_App.dbo.fs_cust_contact AS T1
INNER JOIN testsite_Live_App.dbo.custaddr AS T2
ON T1.cust_num = T2.cust_num
AND T1.cust_seq = T2.cust_seq
INNER JOIN testsite_Live_App.dbo.customer AS T3
ON T2.cust_num = T3.cust_num
AND T2.cust_seq = T3.cust_seq
WHERE T1.email = @EmailAddress
OPEN Service_SC_testsite_Cursor
FETCH NEXT FROM Service_SC_testsite_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Service_SC_testsite_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Service_SC_testsite_Cursor
END
IF @Site IS NULL OR @Site = 'testsite2'
BEGIN
--**************************************
--************testsite2********************
--**************************************
--Customer ShipTo External Email address
DECLARE Cust_ShipTo_testsite2_Cursor CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite2_Live_App.dbo.custaddr AS T1
INNER JOIN testsite2_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.external_email_addr = @EmailAddress
OPEN Cust_ShipTo_testsite2_Cursor
FETCH NEXT FROM Cust_ShipTo_testsite2_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite2'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite2_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_ShipTo_testsite2_Cursor
--Customer ShipTo Internal Email address
DECLARE Cust_ShipTo_testsite2_Cursor2 CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite2_Live_App.dbo.custaddr AS T1
INNER JOIN testsite2_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.internal_email_addr = @EmailAddress
OPEN Cust_ShipTo_testsite2_Cursor2
FETCH NEXT FROM Cust_ShipTo_testsite2_Cursor2
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite2'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite2_Cursor2
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_ShipTo_testsite2_Cursor2
--Customer ShipTo ShipTo Email address
DECLARE Cust_ShipTo_testsite2_Cursor3 CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite2_Live_App.dbo.custaddr AS T1
INNER JOIN testsite2_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.ship_to_email = @EmailAddress
OPEN Cust_ShipTo_testsite2_Cursor3
FETCH NEXT FROM Cust_ShipTo_testsite2_Cursor3
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite2'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite2_Cursor3
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_ShipTo_testsite2_Cursor3
--Customer ShipTo Bill To Email address
DECLARE Cust_ShipTo_testsite2_Cursor4 CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite2_Live_App.dbo.custaddr AS T1
INNER JOIN testsite2_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.bill_to_email = @EmailAddress
OPEN Cust_ShipTo_testsite2_Cursor4
FETCH NEXT FROM Cust_ShipTo_testsite2_Cursor4
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite2'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite2_Cursor4
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
--Prospects Email address
--DECLARE Prospects_testsite2_Cursor CURSOR LOCAL STATIC FOR
-- SELECT T3.company
-- ,T3.addr##1
-- ,T3.city
-- ,T3.state
-- ,T3.country
-- ,T2.prospect_id
-- FROM testsite2_Live_App.dbo.contact AS T1
-- INNER JOIN testsite2_Live_App.dbo.prospect_contact AS T2
-- ON T1.contact_id = T2.contact_id
-- INNER JOIN testsite2_Live_App.dbo.prospect AS T3
-- ON T2.prospect_id = T3.prospect_id
-- WHERE T1.email = @EmailAddress
--OPEN Prospects_testsite2_Cursor
--FETCH NEXT FROM Prospects_testsite2_Cursor
--INTO @Company
-- ,@Addr1
-- ,@City
-- ,@State
-- ,@Country
-- ,@PCI_Num;
--WHILE @@FETCH_STATUS = 0
--BEGIN
-- INSERT INTO #search_results (
-- siteid
-- ,contact_type
-- ,company_name
-- ,address1
-- ,city
-- ,state
-- ,country
-- ,pci_num
-- ,seq)
-- VALUES( 'testsite2'
-- ,'Prospect'
-- ,@Company
-- ,@Addr1
-- ,@City
-- ,@State
-- ,@Country
-- ,@PCI_Num
-- ,'0')
-- FETCH NEXT FROM Prospects_testsite2_Cursor
-- INTO @Company
-- ,@Addr1
-- ,@City
-- ,@State
-- ,@Country
-- ,@PCI_Num;
--END
--CLOSE Prospects_testsite2_Cursor
--Customers ShipTo SC Email address
DECLARE Cust_SC_testsite2_Cursor CURSOR LOCAL STATIC FOR
SELECT T3.name
,T3.addr##1
,T3.city
,T3.state
,T3.country
,T3.cust_num
,T3.cust_seq
FROM testsite2_Live_App.dbo.contact AS T1
INNER JOIN testsite2_Live_App.dbo.customer_contact AS T2
ON T1.contact_id = T2.contact_id
INNER JOIN testsite2_Live_App.dbo.custaddr AS T3
ON T2.cust_num = T3.cust_num
AND T2.cust_seq = T3.cust_seq
INNER JOIN testsite2_Live_App.dbo.customer AS T4
ON T3.cust_num = T4.cust_num
AND T3.cust_seq = T4.cust_seq
WHERE T1.email = @EmailAddress
OPEN Cust_SC_testsite2_Cursor
FETCH NEXT FROM Cust_SC_testsite2_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite2'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_SC_testsite2_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_SC_testsite2_Cursor
--Service Contacts Email address
DECLARE Service_SC_testsite2_Cursor CURSOR LOCAL STATIC FOR
SELECT T2.name
,T2.addr##1
,T2.city
,T2.state
,T2.country
,T2.cust_num
,T2.cust_seq
FROM testsite2_Live_App.dbo.fs_cust_contact AS T1
INNER JOIN testsite2_Live_App.dbo.custaddr AS T2
ON T1.cust_num = T2.cust_num
AND T1.cust_seq = T2.cust_seq
INNER JOIN testsite2_Live_App.dbo.customer AS T3
ON T2.cust_num = T3.cust_num
AND T2.cust_seq = T3.cust_seq
WHERE T1.email = @EmailAddress
OPEN Service_SC_testsite2_Cursor
FETCH NEXT FROM Service_SC_testsite2_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite2'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Service_SC_testsite2_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Service_SC_testsite2_Cursor
END
IF @Site IS NULL OR @Site = 'testsite3'
BEGIN
--**************************************
--************testsite3********************
--**************************************
--Customer ShipTo External Email address
DECLARE Cust_ShipTo_testsite3_Cursor CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite3_Live_App.dbo.custaddr AS T1
INNER JOIN testsite3_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.external_email_addr = @EmailAddress
OPEN Cust_ShipTo_testsite3_Cursor
FETCH NEXT FROM Cust_ShipTo_testsite3_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite3'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite3_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_ShipTo_testsite3_Cursor
--Customer ShipTo Internal Email address
DECLARE Cust_ShipTo_testsite3_Cursor2 CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite3_Live_App.dbo.custaddr AS T1
INNER JOIN testsite3_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.internal_email_addr = @EmailAddress
OPEN Cust_ShipTo_testsite3_Cursor2
FETCH NEXT FROM Cust_ShipTo_testsite3_Cursor2
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite3'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite3_Cursor2
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_ShipTo_testsite3_Cursor2
--Customer ShipTo ShipTo Email address
DECLARE Cust_ShipTo_testsite3_Cursor3 CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite3_Live_App.dbo.custaddr AS T1
INNER JOIN testsite3_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.ship_to_email = @EmailAddress
OPEN Cust_ShipTo_testsite3_Cursor3
FETCH NEXT FROM Cust_ShipTo_testsite3_Cursor3
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite3'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite3_Cursor3
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_ShipTo_testsite3_Cursor3
--Customer ShipTo Bill To Email address
DECLARE Cust_ShipTo_testsite3_Cursor4 CURSOR LOCAL STATIC FOR
SELECT T1.name
,T1.addr##1
,T1.city
,T1.state
,T1.country
,T1.cust_num
,T1.cust_seq
FROM testsite3_Live_App.dbo.custaddr AS T1
INNER JOIN testsite3_Live_App.dbo.customer AS T2
ON T1.cust_num = T2.cust_num
WHERE T1.bill_to_email = @EmailAddress
OPEN Cust_ShipTo_testsite3_Cursor4
FETCH NEXT FROM Cust_ShipTo_testsite3_Cursor4
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite3'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_ShipTo_testsite3_Cursor4
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
--Prospects Email address
--DECLARE Prospects_testsite3_Cursor CURSOR LOCAL STATIC FOR
-- SELECT T3.company
-- ,T3.addr##1
-- ,T3.city
-- ,T3.state
-- ,T3.country
-- ,T2.prospect_id
-- FROM testsite3_Live_App.dbo.contact AS T1
-- INNER JOIN testsite3_Live_App.dbo.prospect_contact AS T2
-- ON T1.contact_id = T2.contact_id
-- INNER JOIN testsite3_Live_App.dbo.prospect AS T3
-- ON T2.prospect_id = T3.prospect_id
-- WHERE T1.email = @EmailAddress
--OPEN Prospects_testsite3_Cursor
--FETCH NEXT FROM Prospects_testsite3_Cursor
--INTO @Company
-- ,@Addr1
-- ,@City
-- ,@State
-- ,@Country
-- ,@PCI_Num;
--WHILE @@FETCH_STATUS = 0
--BEGIN
-- INSERT INTO #search_results (
-- siteid
-- ,contact_type
-- ,company_name
-- ,address1
-- ,city
-- ,state
-- ,country
-- ,pci_num
-- ,seq)
-- VALUES( 'testsite3'
-- ,'Prospect'
-- ,@Company
-- ,@Addr1
-- ,@City
-- ,@State
-- ,@Country
-- ,@PCI_Num
-- ,'0')
-- FETCH NEXT FROM Prospects_testsite3_Cursor
-- INTO @Company
-- ,@Addr1
-- ,@City
-- ,@State
-- ,@Country
-- ,@PCI_Num;
--END
--CLOSE Prospects_testsite3_Cursor
--Customers ShipTo SC Email address
DECLARE Cust_SC_testsite3_Cursor CURSOR LOCAL STATIC FOR
SELECT T3.name
,T3.addr##1
,T3.city
,T3.state
,T3.country
,T3.cust_num
,T3.cust_seq
FROM testsite3_Live_App.dbo.contact AS T1
INNER JOIN testsite3_Live_App.dbo.customer_contact AS T2
ON T1.contact_id = T2.contact_id
INNER JOIN testsite3_Live_App.dbo.custaddr AS T3
ON T2.cust_num = T3.cust_num
AND T2.cust_seq = T3.cust_seq
INNER JOIN testsite3_Live_App.dbo.customer AS T4
ON T3.cust_num = T4.cust_num
AND T3.cust_seq = T4.cust_seq
WHERE T1.email = @EmailAddress
OPEN Cust_SC_testsite3_Cursor
FETCH NEXT FROM Cust_SC_testsite3_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite3'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Cust_SC_testsite3_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Cust_SC_testsite3_Cursor
--Service Contacts Email address
DECLARE Service_SC_testsite3_Cursor CURSOR LOCAL STATIC FOR
SELECT T2.name
,T2.addr##1
,T2.city
,T2.state
,T2.country
,T2.cust_num
,T2.cust_seq
FROM testsite3_Live_App.dbo.fs_cust_contact AS T1
INNER JOIN testsite3_Live_App.dbo.custaddr AS T2
ON T1.cust_num = T2.cust_num
AND T1.cust_seq = T2.cust_seq
INNER JOIN testsite3_Live_App.dbo.customer AS T3
ON T2.cust_num = T3.cust_num
AND T2.cust_seq = T3.cust_seq
WHERE T1.email = @EmailAddress
OPEN Service_SC_testsite3_Cursor
FETCH NEXT FROM Service_SC_testsite3_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #search_results (
siteid
,contact_type
,company_name
,address1
,city
,state
,country
,pci_num
,seq)
VALUES( 'testsite3'
,'Customer'
,@Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq)
FETCH NEXT FROM Service_SC_testsite3_Cursor
INTO @Company
,@Addr1
,@City
,@State
,@Country
,@PCI_Num
,@Seq;
END
CLOSE Service_SC_testsite3_Cursor
END
SELECT DISTINCT TOP 50 *
FROM #search_results
ORDER BY company_name, siteid
END