June 12, 2006 at 7:11 am
I'm rather new to t-sql - i've an existing table of postal address tblAddr and i've decided to add table to store alternative addresses tblAltAddr and another table to store telephone numbers tblTel linked to lead address in tblAddr.
A few existing rows in tblAddr concern addresses which should be in tblAltAddr and i've a stored procedure that accepts ID of row to be moved, and ID of lead row and moves the address using insert and delete statements.
I can find the rows that should be moved using a select statement that find duplicate names. I don't think that a stored procedure can accept the resultset from that select statement.
So i think i need insert / update / delete statements that filter using the duplicate select statement.
which is a shame since the logic is embodied in the stored procedure.
what's best practice in this situation? thanks for your help.
Dave
June 12, 2006 at 8:11 am
There are a couple of different ways this could be handled and still use the SP you have created.
1. Load the results of your select statement into a temp table that has an identity field also. Then loop through this temp table calling your SP for each record.
DECLARE @iLine int, @iMaxLine int, @iLead int, @iDupe int
CREATE TABLE #TmpTable (iLineId int IDENTITY(1,1) primary key, iLeadId int, iDupeId int)
INSERT INTO #TmpTable (iLeadId, iDupeId)
{your select statement to find the dupes}
SELECT @iLine = MIN(iLineId), @iMaxLine = MAX(iLineId)
FROM #TmpTable
WHILE @iLine <= @iMaxLine
BEGIN
SELECT @iLead = iLeadId, @iDupe = iDupeId FROM #TmpTable WHERE iLineId = @iLine
{sp name} @iLead, @iDupe
SET @iLine = @iLine + 1
END
2. The second method would require you to make some changes to your SP moving the while loop into it. Outside of the SP, you would build a string array using commas as a delimiter. You would need to use the CHARINDEX function to parse the array for each value.
I think you will have better luck with the first method because it does not require any changes to your SP. However, if you would like to see an example of the second method, let me know.
Dave N
June 12, 2006 at 9:49 am
Thanks Dave, i'll give that a go.
June 13, 2006 at 7:58 am
In your place I probably wouldn't create a new table to store alternative adresses. If it is an address, store it together with other addresses; depending on the overall design, you can either add identifying column to the table tblAddr (with values meaning Main / Alternative.. or other... some companies have special address for invoicing, another for mail delivery, and a fully different official company address) or define this property using the table that joins persons/companies and their addresses.
June 15, 2006 at 2:10 am
That makes more sense ... and then join to tblTel allowing multiple tel numbers for each address.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply