Matt Goodman Matt Goodman - 4 months ago 11
SQL Question

Set a declared variable using a selection expression containing joins?

Early in my code, I have declared a variable (among other variables):

DECLARE @ZIPFk int;


Then, shortly thereafter, I try to SET the above variable's value using a selection expression that contains two joins:

SET @ZIPFk=(SELECT Zip.id
FROM Zip
INNER JOIN Zip5 ON Zip5.id=Zip.Zip5Fk
JOIN PlaceName ON Zip5.PlaceNameFk=PlaceName.id
WHERE PlaceName.PlaceName=@USPSComm AND Zip5.ZipCode5=@ZIPCode)


It's not working and I can't figure out why. My resulting table just has a NULL value in the field where I expect/want the @ZIPFk value to be populated. When run on its own, apart from the declared variables, the SELECTION expression does result in a table with a single row/single column/single value.

Here is a look at the end of the query where the actual UPDATE occurs, using the declared variable:

UPDATE CompleteSubaddressZip
SET ZipFk=@ZIPFk
FROM CompleteSubaddressZip as a
JOIN CompleteSubaddress AS b ON b.id=a.CompleteSubaddressFk
WHERE (b.AddressUUID=@SelectedUUID)


And...here is a look at the entire thing:

USE MyDatabase;
DECLARE @USPSComm varchar = 'Springville' --<--Enter USPS ZIP community name here
DECLARE @ZIPCode varchar = '99221' --<--Enter USPS ZIP Code here
DECLARE @RowsToProcess int;
DECLARE @ZIPFk int;
DECLARE @CurrentRow int;
DECLARE @SelectedUUID uniqueidentifier;
DECLARE @UUIDTable table (RowID int not null primary key identity (1,1), UUID uniqueidentifier);

SET @ZIPFk=(SELECT a.id
FROM dbo.Zip AS a
INNER JOIN Zip5 as b ON b.id=a.Zip5Fk
JOIN PlaceName as c ON b.PlaceNameFk=c.id
WHERE c.PlaceName=@USPSComm AND b.ZipCode5=@ZIPCode)

INSERT INTO @UUIDTable (UUID) VALUES
( 'f4x6faf4-bxx2-4166-bcd6-88c9af8ab546'), ('3e20xx5e-5edc-4735-8962-5ab3c50d053e'),
('696xxB2D-50DE-4361-807D-48xxD2AC5CxB')
, ('c5879x41-e144-4ecc-8056-5493exxx1a5'), ('af7993cf-2xec-4c5f-b02e-92cb6dxxxf2b')

SET @RowsToProcess =@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow =@CurrentRow+1
SELECT
@SelectedUUID=UUID
FROM @UUIDTable
WHERE RowID=@CurrentRow

UPDATE CompleteSubaddressZip
SET ZipFk=@ZIPFk
FROM CompleteSubaddressZip as a
JOIN CompleteSubaddress AS b ON b.id=a.CompleteSubaddressFk
WHERE (b.AddressUUID=@SelectedUUID)
END

Answer

I apologize to any who might read this thread: my fumbling around and the fact that I'm not really sure what I've changed to correct my problem, renders this thread nearly useless.

However, for posterity, here is my final(?) query which actually works as intended. (I owe many thanks to the comments from user "Matt" who helped me clean things up and at least got me on the right track, and tell me when I was being daft). I took out the 'looping' and removed unnecessary variables and generally simplified everything:

USE MyDatabase;
DECLARE @USPSComm varchar (20)='Springfield'; --  <--Enter USPS ZIP community name here 
DECLARE @ZIPCode varchar (5)='97477'; --  <--Enter USPS ZIP Code here
DECLARE @ZIPFk int;

SET @ZIPFk=(SELECT x.id
FROM Zip AS x
INNER JOIN Zip5 as y ON y.id=x.Zip5Fk
JOIN PlaceName as z ON y.PlaceNameFk=z.id
WHERE z.PlaceName=@USPSComm AND y.ZipCode5=@ZIPCode);

UPDATE a
SET ZipFk=@ZIPFk
FROM CompleteSubaddressZip as a
JOIN CompleteSubaddress AS b ON b.id=a.CompleteSubaddressFk
JOIN Zip as c ON a.ZipFk=c.id
JOIN Zip5 as d ON c.Zip5Fk=d.id
JOIN PlaceName as e ON d.PlaceNameFk=e.id  
WHERE b.AddressUUID IN ('xxxxxxxx-xxxx-UUID-xxxx-xxxxxxxxxxxx'
, 'xxxxxxxx-xxxx-UUID-xxxx-xxxxxxxxxxxx'
, 'xxxxxxxx-xxxx-UUID-xxxx-xxxxxxxxxxxx'
, 'xxxxxxxx-xxxx-UUID-xxxx-xxxxxxxxxxxx'
, 'xxxxxxxx-xxxx-UUID-xxxx-xxxxxxxxxxxx');
Comments