update stored proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

update stored proc

Author

Topic

slimjen
Yak Posting Veteran

61 Posts

Posted - 2012-07-12 : 16:02:48

Hi All,
I have a sql server back in and using access 2007 as frontend. I attempted to convert the update queries in access to a stored procedure to speed things up. The stored procedure runs but does not update any rows like it does in Access. Here are my update queries in Access:

UPDATE tblSP_SHIPMENT_temp INNER JOIN dbo_tblCarrierID ON tblSP_SHIPMENT_temp.CARRIERID = dbo_tblCarrierID.txtCARRIERID 
SET tblSP_SHIPMENT_temp.CARRIERNAME = [dbo_tblCARRIERID].[txtCARRIERNAME];
UPDATE tblSP_SHIPMENT_temp INNER JOIN dbo_tblZipcode ON tblSP_SHIPMENT_temp.SENDERZIP = dbo_tblZipcode.txtZIPCODE 
SET tblSP_SHIPMENT_temp.SENDERCITY = [dbo_tblZipcode].[txtCity], tblSP_SHIPMENT_temp.SENDERSTATE = [dbo_tblZipcode].[txtState];
UPDATE tblSP_SHIPMENT_temp INNER JOIN dbo_tblZipcode ON tblSP_SHIPMENT_temp.RECIPIENTZIP = dbo_tblZipcode.txtZIPCODE 
SET tblSP_SHIPMENT_temp.RECIPIENTCITY = [dbo_tblZipcode].[txtCity], tblSP_SHIPMENT_temp.RECIPIENTSTATE = [dbo_tblZipcode].[txtState];
UPDATE tblSP_SHIPMENT_temp INNER JOIN qryCOUNTRYREGION ON tblSP_SHIPMENT_temp.SENDERSTATE = qryCOUNTRYREGION.txtSTATE 
SET tblSP_SHIPMENT_temp.COUNTRYREGSENDER = [qryCOUNTRYREGION].[txtREGIONDESC];
UPDATE tblSP_SHIPMENT_temp INNER JOIN qryCOUNTRYREGION AS qryCOUNTRYREGION_1 ON tblSP_SHIPMENT_temp.RECIPIENTSTATE = qryCOUNTRYREGION_1.txtSTATE 
SET tblSP_SHIPMENT_temp.COUNTRYREGRECIPIENT = [qryCOUNTRYREGION_1].[txtREGIONDESC];

The following is the converted stored proc:

USE [ACFAST]
GO

/****** Object:  StoredProcedure [dbo].[tblUpdateTemp]    Script Date: 07/12/2012 14:39:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE procedure usp_tblUpdateTemp
as

update	tST
set		tST.CARRIERNAME = tCID.txtCARRIERNAME
		,tST.SENDERCITY = tZC1.txtCity
		,tST.SENDERSTATE = tZC1.txtState
		,tST.RECIPIENTCITY = tZC2.txtCity
		,tST.RECIPIENTSTATE = tZC2.txtState
		,tST.COUNTRYREGSENDER = tR1.txtREGIONDESC
		,tST.COUNTRYREGRECIPIENT = tR2.txtREGIONDESC
from	dbo.tblSP_SHIPMENT_temp tST
inner
join	dbo.tblCarrierID tCID with (NOLOCK) on
			tST.CARRIERID = tCID.txtCARRIERID
inner
join	dbo.tblZipcode tZC1 with (NOLOCK) on
			tST.SENDERZIP = tZC1.txtZIPCODE
inner
join	dbo.tblZipcode tZC2 with (NOLOCK) on
			tST.RECIPIENTZIP = tZC1.txtZIPCODE
inner
join	dbo.tblStates tS1 with (NOLOCK) on
			tS1.txtSTATE = tST.SENDERSTATE
inner
join	dbo.tblRegions tR1 with (NOLOCK) on
			tR1.idxREGIONID = tS1.txtREGION
inner
join	dbo.tblStates tS2 with (NOLOCK) on
			tS2.txtSTATE = tST.RECIPIENTSTATE
inner
join	dbo.tblRegions tR2 with (NOLOCK) on
			tR2.idxREGIONID = tS2.txtREGION
	

GO

Can anyone see the problem?
Thanks

edit: to fix long lines

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-12 : 16:09:04

You have turned 5 update statements into only one statement.
In this new statement you are joining all tables together using inner join and if only one join isn't matching then there will be no affected rows...

No, you're never too old to Yak'n'Roll if you're too young to die.

slimjen
Yak Posting Veteran

61 Posts

Posted - 2012-07-12 : 20:37:52

So; is there a way to find out what's failing? Is there a step thru like Access? Sorry; really new to Stored Proc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-13 : 10:44:03

the starting point is to check table data and see if relationship is one to one and whether you'll matches in all tables always based on column relationships defined

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimjen
Yak Posting Veteran

61 Posts

Posted - 2012-07-15 : 22:34:04

Got it to work! Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-15 : 23:40:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources