asked on

UPDATE FUNCTION VALUES TO TABLE

I have an sql function nightmare. My predecessor has created a 16000+ line function which consists of update table functions. Is there a way I can take the values and put them into a table so that I can create one function to do the same thing.

Here is a sample

UPDATE
COMPANYTABLE
SET
COMPANYNAME = N'NEXUS URBAN LIVING APTS',
INCAREOFNAME =N'APARTMENT MANAGER',
COMPANYADDRESS =N'6810 GLENDORA AVE OFC',
COMPANYCITY =N'SAN ANTONIO',
COMPANYSTATE =N'TX',
COMPANYZIP =N'78218',
COMPANYPHONE =N'2104659680',
PLTFCOUNTY =N'DONE'
WHERE
OWNERADDRESS LIKE N'%Glendora%' AND
OWNERZIP =N'78218' AND
COMPANYNAME LIKE N'%NEXUS URBAN LIVING%'

ste5an

You can do something like this:

DECLARE @Data TABLE
    (
        NEW_COMPANYNAME NVARCHAR(255) ,
        NEW_INCAREOFNAME NVARCHAR(255) ,
        NEW_COMPANYADDRESS NVARCHAR(255) ,
        NEW_COMPANYCITY NVARCHAR(255) ,
        NEW_COMPANYSTATE NVARCHAR(255) ,
        NEW_COMPANYZIP NVARCHAR(255) ,
        NEW_COMPANYPHONE NVARCHAR(255) ,
        NEW_PLTFCOUNTY NVARCHAR(255) ,
        FILTER_OWNERADDRESS NVARCHAR(255) ,
        FILTER_OWNERZIP NVARCHAR(255) ,
        FILTER_COMPANYNAME NVARCHAR(255)
    );

INSERT INTO @Data ( NEW_COMPANYNAME ,
                    NEW_INCAREOFNAME ,
                    NEW_COMPANYADDRESS ,
                    NEW_COMPANYCITY ,
                    NEW_COMPANYSTATE ,
                    NEW_COMPANYZIP ,
                    NEW_COMPANYPHONE ,
                    NEW_PLTFCOUNTY ,
                    FILTER_OWNERADDRESS ,
                    FILTER_OWNERZIP ,
                    FILTER_COMPANYNAME )
VALUES ( N'NEXUS URBAN LIVING APTS', N'APARTMENT MANAGER', N'6810 GLENDORA AVE OFC', N'SAN ANTONIO', N'TX', N'78218', N'2104659680', N'DONE', N'%Glendora%' ,
         N'78218' , N'%NEXUS URBAN LIVING%' );

UPDATE CT
SET    CT.COMPANYNAME = D.NEW_COMPANYNAME ,
       CT.INCAREOFNAME = D.NEW_INCAREOFNAME ,
       CT.COMPANYADDRESS = D.NEW_COMPANYADDRESS ,
       CT.COMPANYCITY = D.NEW_COMPANYCITY ,
       CT.COMPANYSTATE = D.NEW_COMPANYSTATE ,
       CT.COMPANYZIP = D.NEW_COMPANYZIP ,
       CT.COMPANYPHONE = D.NEW_COMPANYPHONE ,
       CT.PLTFCOUNTY = D.NEW_PLTFCOUNTY
FROM   COMPANYTABLE CT
       INNER JOIN @Data D ON CT.OWNERADDRESS LIKE D.FILTER_OWNERADDRESS
                             AND CT.OWNERZIP = D.FILTER_OWNERZIP
                             AND CT.COMPANYNAME LIKE D.FILTER_COMPANYNAME;

Open in new window

But I'm not sure, whether this is advisable.

p.s.

+ line function which consists of update table functions

Incorrect naming. These are UPDATE statements.

p.p.s.
depending on the use-case, they are absolutely acceptable. Maybe they are even scripted.

Scott Pletcher

For me to provide a reasoned response, I would need to see at least several of the UPDATEs in a row from the procedure (?! I don't think a function can UPDATE tables).

You'd definitely want to put these conditions in a table, if nothing else for efficiency, and for ease of maintenance.

G Godwin

So, are you saying the 16000+ line function has these values hard coded?

If so, it is definitely possible. The complexity will depend on the rest of the update statements in the function.

Here is an example (using temp tables) which uses a table to update any number of records in another table. Depending on what the function does, a Stored Procedure would likely be a better object for this type of work. The example below could be worked into a procedure to do what you desire.

/*
------------------------------------
-- YOUR SAMPLE

UPDATE
     COMPANYTABLE
SET
     COMPANYNAME	=N'NEXUS URBAN LIVING APTS',
     INCAREOFNAME	=N'APARTMENT MANAGER',
     COMPANYADDRESS =N'6810 GLENDORA AVE OFC',
     COMPANYCITY	=N'SAN ANTONIO',
     COMPANYSTATE	=N'TX',
     COMPANYZIP		=N'78218',
     COMPANYPHONE	=N'2104659680',
     PLTFCOUNTY		=N'DONE'
WHERE
     OWNERADDRESS LIKE N'%Glendora%' AND 
     OWNERZIP =N'78218' AND 
     COMPANYNAME LIKE N'%NEXUS URBAN LIVING%'

 
------------------------------------
*/



if exists (select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')  
   and o.id = object_id(N'tempdb..#TEMP_COMPANYTABLE'))
DROP TABLE #TEMP_COMPANYTABLE;


create table #TEMP_COMPANYTABLE 
(
	   ID				int identity Primary Key
     , COMPANYNAME		nvarchar(150)
     , INCAREOFNAME		nvarchar(150)
     , COMPANYADDRESS	nvarchar(150)
     , COMPANYCITY		nvarchar(150)
     , COMPANYSTATE		nvarchar(150)
     , COMPANYZIP		nvarchar(10)
     , COMPANYPHONE		nvarchar(150)
     , PLTFCOUNTY		nvarchar(150)
     , OWNERADDRESS		nvarchar(150)
	 , OWNERZIP			nvarchar(10)
)

insert into #TEMP_COMPANYTABLE (COMPANYNAME, INCAREOFNAME, COMPANYADDRESS, COMPANYCITY, COMPANYSTATE, COMPANYZIP, COMPANYPHONE, PLTFCOUNTY, OWNERADDRESS, OWNERZIP)
values 
('NEXUS URBAN LIVING APTS', 'APARTMENT MANAGER', '6810 GLENDORA AVE OFC', 'SAN ANTONIO', 'TX', '78218', '2104659680', 'DONE', '6810 GLENDORA AVE OFC','78218')
, ('NEXUS RURAL LIVING APTS', 'APARTMENT MANAGER', '6810 PANDORA AVE OFC', 'SAN BERNADINO', 'CA', '92354', '9094659680', 'NOT DONE', '6810 PANDORA AVE OFC','92354')

select 'ORIGINAL RECORDS'
select * From #TEMP_COMPANYTABLE

if exists (select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')  
   and o.id = object_id(N'tempdb..#TEMP_CompanyUpdates'))
DROP TABLE #TEMP_CompanyUpdates;


create table #TEMP_CompanyUpdates 
(
	  ID					int identity primary key 
	, SearchCompanyName		nvarchar(150)
	, SearchOwnerAddress	nvarchar(150)
	, SearchOwnerZip		nvarchar(10)
	, NewCOMPANYNAME		nvarchar(150)
	, NewINCAREOFNAME		nvarchar(150)
	, NewCOMPANYADDRESS		nvarchar(150)
	, NewCOMPANYCITY		nvarchar(150)
	, NewCOMPANYSTATE		nchar(2)
	, NewCOMPANYZIP			nvarchar(150)
	, NewCOMPANYPHONE		nvarchar(50)
	, NewPLTFCOUNTY			nvarchar(150)
)

create unique index uix_CoUpdt_001 on #TEMP_CompanyUpdates (SearchOwnerAddress, SearchOwnerZip, SearchCompanyName)

insert into #TEMP_CompanyUpdates (SearchCompanyName, SearchOwnerAddress, SearchOwnerZip, NewCOMPANYNAME, NewINCAREOFNAME, NewCOMPANYADDRESS, NewCOMPANYCITY, NewCOMPANYSTATE, NewCOMPANYZIP, NewCOMPANYPHONE, NewPLTFCOUNTY)
values 
 ('NEXUS URBAN LIVING', 'Glendora', '78218', 'NEW - NEXUS URBAN LIVING APTS', 'NEW - APARTMENT MANAGER', 'NEW - 6810 GLENDORA AVE OFC', 'NEW - SAN ANTONIO', 'TX', 'NEW-78218', 'NEW - 2104659680', 'NEW - DONE')
,('NEXUS RURAL LIVING', 'PANDORA', '92354', 'NEW - NEXUS RURAL LIVING APTS', 'NEW - APARTMENT MANAGER', 'NEW - 6810 PANDORA AVE OFC', 'NEW - SAN BERNADINO', 'CA', 'NEW-92354', 'NEW - 9094659680', 'NEW - NOT DONE')


select 'UPDATE TABLE'
select * From #TEMP_CompanyUpdates


UPDATE
     COTBL
SET
     COMPANYNAME	= NewCOMPANYNAME	
   , INCAREOFNAME	= NewINCAREOFNAME	
   , COMPANYADDRESS = NewCOMPANYADDRESS 
   , COMPANYCITY	= NewCOMPANYCITY	
   , COMPANYSTATE	= NewCOMPANYSTATE	
   , COMPANYZIP		= NewCOMPANYZIP		
   , COMPANYPHONE	= NewCOMPANYPHONE	
   , PLTFCOUNTY		= NewPLTFCOUNTY		
from #TEMP_COMPANYTABLE COTBL
join #TEMP_CompanyUpdates CU on 
         COTBL.OWNERADDRESS LIKE '%' + CU.searchOwnerAddress + '%' 
	 AND COTBL.OWNERZIP = CU.SearchOwnerZip  
	 AND COTBL.COMPANYNAME LIKE '%' + CU.SearchCompanyName + '%'

SELECT 'MODIFIED RECORDS'
select * From #TEMP_COMPANYTABLE

Open in new window

ASKER CERTIFIED SOLUTION

Connie Jerdet-Skehan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Connie Jerdet-Skehan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial