August 26, 2011 at 12:47 pm
DECLARE cst CURSOR FOR
SELECT ac , id ,ov, nw from @Acct_Stack_Temp
OPEN UDPATE_ACCT_STACKHOLDER
FETCH NEXT FROM cst INTO @ac,@id,@OldUName,@NewUName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @OldUName IS NULL
BEGIN
SET @OldUName = ''TBD''
END
IF @NewUName IS NULL
BEGIN
SET @NewUName = ''TBD''
END
select top 1 @old = Ads_Id from Dime where DimVal in (@OldUName) and Dimensionid = @id
select top 1 @new = Ads_Id from Dime where DimVal in (@NewUName) and Dimensionid = @id
IF @id = 11
BEGIN
if EXISTS (Select ROLE_ID FROM ACCT_STACK WHERE ACCT_ID = @ac and [USER_ID] = @old AND ROLE_ID = 1)
begin
UPDATE ACCT_STACK SET USER_ID = @new where ACCT_ID = @ac and [USER_ID] = @old AND ROLE_ID = 1
end
ELSE
INSERT INTO ACCT_STACKE VALUES (@ac,@new,1,GETDATE())
END
FETCH NEXT FROM cst INTO @ac,@id,@OldUName,@NewUName
END
CLOSE UDPATE_ACCT_STACKHOLDER
DEALLOCATE UDPATE_ACCT_STACKHOLDER
August 26, 2011 at 1:10 pm
Can you provide the DDL for the objects not defined in the script and sample data? What you have doesn't make it clear why you're doing the top 1 on the values from the Dime table so we don't know the best way to get that value.
At the very least, you can change the cursor to this and it'll a couple several steps inside it:
SELECT ac, id,
isnull(ov, 'TDB') as [ov], isnull(nw, 'TBD') as [nv] --Eliminates the need to do this later
from @Acct_Stack_Temp
where id = 11 --It looks like this is the only case in which the cursor does something so it'll eliminate going through rows.
If I'm right, and without more details this is just a guess, this will get the data you need:
WITH topdime AS (SELECT ROW_NUMBER() OVER (ORDER BY dimensionid) [ROW], ads_id, dimval, Dimensionid
FROM dime)
SELECT ac, olddime.ads_id, newdime.ads_id
FROM @Acct_Stack_Temp a
INNER JOIN topdime olddime ON a.id = olddime.dimensionid
AND ISNULL(ov, 'TDB') = olddime.dimval
AND olddime.row = 1
INNER JOIN topdime newdime ON a.id = newdime.dimensionid
AND ISNULL(nw, 'TDB') = newdime.dimval
AND newdime.row = 1
WHERE id = 11
August 26, 2011 at 9:04 pm
Thank u cfradenburg. ya, It gives exact result what I want.
Exactly My Requirement is,
1.want to fetch the respective id from Dime for the user(either olduser or newUser) from @ Acct_Stack_Temp.
Thats why I take ads_id one by one from Dime table.
2. Update the NewId instead of OldID in ACCT_STACK.
Thank u so much brother.:-)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply