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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply