2 Updates in a Stored Proc?

Question

Hello! I can't figure this one out. Maybe you all can help please?

When I had just the first UPDATE stmt in my Stored Proc

update dbo.Property

Everything worked perfectly, BUT When I added the 2nd one to update a second table

UPDATE dbo.[LineItemInfo]

nothing is updated in either table. Anyone have any ideas? I would greatly apprecaite it!
Thanks!
Mike

USE [BAPropertyTax]
GO
/****** Object:  StoredProcedure [dbo].[UpdateStagingTable]    Script Date: 08/17/2012 14:30:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateStagingTable]
    @CASEID nvarchar(255),
    @County nvarchar(255),
    @Address nvarchar(255),
    @City nvarchar(255),
    @State nvarchar(255),
    @Zip nvarchar(255),
    @AcquisitionDate nvarchar(255),
    @Step nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;

    update dbo.Property

    set PCounty=@County,
       PAddress = @Address,
       PCity = @City,
       PState = @State,
       PZip = @Zip,
       PAcquisitionDate = @AcquisitionDate,
       PCurrentStep = @Step
    where PCaseId=@CASEID
   
    UPDATE dbo.[LineItemInfo]
 SET        LIStateDate = cp.[Tax Service2]
 FROM     dbo.[Destination - YARDI_REPORTS$] AS cp INNER JOIN
                dbo.[LineItemInfo] AS mp ON mp.LIPropCaseID = cp.[CaseID]
                WHERE mp.LIPropCaseId = @CASEID
END

---

Mike Kiser

Answer 1

Check the query if it is returning any result by passing caseid!

Select cp.[Tax Service2] 
 FROM     dbo.[Destination - YARDI_REPORTS$] AS cp INNER JOIN
                dbo.[LineItemInfo] AS mp ON mp.LIPropCaseID = cp.[CaseID]
                WHERE mp.LIPropCaseId = @CASEID

--

UPDATE mp
 SET   mp.LIStateDate = cp.[Tax Service2] 
 FROM  dbo.[LineItemInfo]  mp
 INNER JOIN dbo.[Destination - YARDI_REPORTS$] cp ON mp.LIPropCaseID = cp.[CaseID]
 WHERE mp.LIPropCaseId = @CASEID
                

Answer 2

Where is the DDL?

Why are you inviting bad data in Chinese Unicode with those data types? The length is insane. Why are you violating ISO-11179 rules for data element names? You do know that there are ANSI Standards county codes, etc.?

I will bet that your Something_Line_Items table references the Properties table rows that are not committed yet. We also do not use the old UPDATE.. FROM syntax today. Look at MERGE statement. Here is your proc header with data types and sources, so you can fix your DDL with CHECK() constraints.

Oh, staging tables are not updated; youn scrub data in them, move the clean data to the production DB and flush them out. You do not want them to be around very long; they are the Emergency room cases of data :)


CREATE PROCEDURE Load_Properties_Stagingtable
 @in_case_id VARCHAR(255),
 @in_county_code CHAR(3), --ANSI 
 @in_street_address VARCHAR(35), --USPS
 @in_city_name VARCHAR(25), --USPS
 @in_state_code CHAR(2), --USPS
 @in_zip CHAR(5), --USPS
 @in_acquisition_date DATE,
 @in_step VARCHAR(255) – no idea what type!
AS ..

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 3

Make sure the UPDATE syntax and cardinality correct:

http://www.sqlusa.com/articles2005/sqlupdate/

You can also use MERGE (update only mode) instead of UPDATE:

http://www.sqlusa.com/bestpractices/merge-for-update/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING