June 19, 2008 at 12:49 pm
Hi this is my sp
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.
here based on value col data test@test.com has to replace with 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:47 pm
First, why update the "value" column? It's already equal to the thing you're updating it to.
Second, you have variables in the proc with different names than when you declared them (in the input parameters part).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 1:55 pm
anjan.ashok (6/19/2008)
Hi this is my spCREATE 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.
here based on value col data test@test.com has to replace with anj@test.com
i can do that by running update query manually but when i run sp its not working pls help me
This doesn't make any sense, you have defined the input parameters as: @strcvalue and @strdesc and trying to use @strconfigvalue and @strconfigdesc. I am going to assume that this is a typo.
If you are trying to modify the existing value - you are going to need to provide the old value and the new value. So, you could do something like:
CREATE PROCEDURE dbo.MyReplaceValue
@oldValue varchar(100)
,@newValue varchar(100)
,@newDescription varchar(100)
AS
UPDATE testtbl
SET value = @newValue
,desc = @newDescription
WHERE value = @oldValue;
BTW - don't create your procedures with 'sp_'. SQL Server will try to lookup any procedure that begins with 'sp_' in the master database.
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2008 at 12:26 am
>>i can do that by running update query manually but when i run sp its not working pls help me
You mean following query is working. Right?
update testtbl set value='anj@test.com' where value='test@test.com'
Rewrite your SP as follows:
CREATE PROCEDURE usp_replace_value (
@strcvalueOld varchar(100),
@strcvalueNew varchar(100),
@strdesc nvarchar(100)
)
as
update testtbl set value=@strconfigvalueNew,desc=@strconfigdesc where value=@strconfigvalueOld
GO
execute usp_replace_value @strcvalueOld = 'test@test.com', @strcvalueNew = 'anj@test.com' , @strdesc = 'Any Value'
June 20, 2008 at 6:27 am
FYI, this is a repost of another discussion going through the same perambulations over here.
http://qa.sqlservercentral.com/Forums/Topic520048-338-1.aspx
----------------------------------------------------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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply