June 19, 2008 at 11:12 am
Hi,
I need to write a SP which is used to find the old email id which we pass and replace with the new email id which we will give.
Tbl_data is table name and here the col name is email_id,
for eg: if i pass the old mail id as anja@test.com it should find this email id and replace with john@test.com
Pls can anyone give the stored procedure to do this,i am also trying.
Its my humble request.pls
Waiting for ur kind reply
June 19, 2008 at 11:34 am
You mean something like this?
CREATE PROCEDURE UpdateEmail
(@OldEmail nvarchar(275)
,@NewEmail nvarchar(275)
AS
BEGIN TRY
UPDATE MyEmail
SET Email = @NewEmail
WHERE Email = @OldEmail
END
BEGIN CATCH
RETURN ERROR_NUMBER();
END
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 19, 2008 at 11:44 am
yes grant thanks for the help
but i need one more help pls
like this procedure only ,can we pass oldmail and new mail as parameter
exec updateemail @oldmail='anj@test.com',@newemail='grant@test.com'
here we have only col as email_id,
whereever emailid=anj@test.com it should replace to grant@test.com
after running this procedure.
waiting for ur kind reply
June 19, 2008 at 11:51 am
You should be able to take the same structures that I supplied and modify it for a different column and/or data type. It all works the same way.
UPDATE table
SET col1 = @somevalue
,col2 = @someothervalue
WHERE col3 = @yetanothervalue
Please take a look at the UPDATE statement in the Books Online (those are the help files that came with SQL Server).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 19, 2008 at 12:55 pm
hi grant see my sp pls and help me
CREATE PROCEDURE sp_replace_value (
@strcvalue varchar(100),
@strdesc nvarchar(100)
)
as
update testtbl set value=@strconfigvalue,desc=@strconfigdesc where value=@strconfigvalue
here assume we have value column data is test@test.com, i need to replace test@test.com to anj@test.com in the value column and desc the column also.
whereever the colname value data is test@test.com it should to anj@test.com
i can do that by running update query manually but when i run sp its not working pls help me
June 19, 2008 at 1:09 pm
anjan.ashok (6/19/2008)
hi grant see my sp pls and help meCREATE PROCEDURE sp_replace_value (
@strcvalue varchar(100),
@strdesc nvarchar(100)
)
as
update testtbl set value=@strconfigvalue,desc=@strconfigdesc where value=@strconfigvalue
here assume we have value column data is test@test.com, i need to replace test@test.com to anj@test.com in the value column and desc the column also.
whereever the colname value data is test@test.com it should to anj@test.com
i can do that by running update query manually but when i run sp its not working pls help me
Take a look at your query. You defined a set of parameters, but you're using a different set of parameters. They have to match.
If you're getting specific errors, please post them.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 19, 2008 at 1:20 pm
CREATE PROCEDURE sp_replace_value (
@strcvalue varchar(100),
@strdesc nvarchar(100)
)
as
update testtbl set value=@strcvalue(anj@test.com),desc=@strdesc where value=@strcvalue('test@test.com')
here i am not getting any error its showing zero rows effected.
in where condition i am passing value='test@test.com' and value column it has to be replaced with 'anj@test.com
i tried no errors but zero rows affected can help me
June 20, 2008 at 5:28 am
You're using the parameter twice, once to set & once to search. It can't be done like that. You have to refer to the column twice, but if you have three variable values, you need three parameters.
This works:
CREATE TABLE testtbl
([value] VARCHAR(100)
,[desc] NVARCHAR(100)
)
GO
CREATE PROCEDURE sp_replace_value (
@strcvalue varchar(100),
@strdesc nvarchar(100),
@oldstrcvalue VARCHAR(100)
)
as
update testtbl set [value]=@strcvalue,[desc]=@strdesc where value=@oldstrcvalue
GO
INSERT INTO [testtbl] (
[value],
[desc]
) VALUES ( 'joe@mama.com',
'some value' )
GO
EXEC [sp_replace_value]
@strcvalue = 'jane@mama.com', -- varchar(100)
@strdesc = N'someothervalue', -- nvarchar(100)
@oldstrcvalue = 'joe@mama.com' -- VARCHAR(100)
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply