February 10, 2009 at 1:26 pm
Hi all, I am trying to avoid dynamic sqls within a stored proc. Thsi procedure takes 2 parameters @oldId and @newid. Some tables of the database has this parameter as column so an update works fine but for some tables this value is a concatenated value. Ex
CREATE procedure proc_ChangeID
@OldId varchar(300), @NewID varchar(50) as
select @sqlstring=N'update Table1 set col1 = replace(userid, ''' + @OldID + ''', '''+@NewID+''')'
exec sp_executesql @sqlstring
select @sqlstring=N'update Table2 set col2= ''' + @NewID + '''where col2 = ''' + @OldID + ''''
exec sp_executesql @sqlstring
February 10, 2009 at 1:28 pm
Sorry forgot to add this to the original post..
For the 2nd part dynamic sqls can be easily eliminated but is there an easy way to eliminate in 1st update . Reason being in the first update, id passed is a concatenated value in Table1 and needs to be replaced only for the pased value. Does anyone have a solution to have it as a straight update . any help will be appreciated. Thanks
February 10, 2009 at 4:18 pm
Cannot understand why
update Table1 set col1 = replace(userid, @OldID, @NewID)
does not work for you.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply