Stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure

Author

Topic

sqlstarter1
Starting Member

6 Posts

Posted - 2009-04-27 : 11:45:49

Create table myProduct (i int, j int, t text)
go
create unique index ui on myProduct
(i, j)
go
create proc nr_Adtxt
@i int ,
@j int ,
@t varchar(8000) ,
@Action varchar(1) -- 'I insert
as
declare @ptr binary(16)

if @Action = 'I'
begin
insert myProduct
(
i ,
j ,
t
)
select @i ,
@j ,
@t
end

if @Action = 'A'
begin
select @ptr = textptr(t)
from mySecond
where i = @i
and j = @j

updatetext myProduct.t @ptr null 0 @t
end

go
exec nr_Adtxt 1, 1, 'abc,bdes,123', 'I'--insert
exec nr_Adtxt 1, 1, '678,es,123', 'A'--update
select i,j, substring(t, 1, 8000) from myProduct

--This code will replace all occurrances of a string in a text column in all rows

delete myProduct
exec nr_Adtxt 1, 1, '6,bdes,123', 'I'
exec nr_Adtxt 1, 1, '678,bdes,123', 'A'
declare @t varchar(8000)
select @t = space(6000) + 'sadf'
exec nr_Adtxt 1, 1, @t, 'A'
select @t = 'sadf'
exec nr_Adtxt 1, 1, @t, 'A'
select @t = space(6000) + 'sadf'
exec nr_Adtxt 1, 1, @t, 'A'
select @t = 'sadf'
exec nr_Adtxt 1, 1, @t, 'A'
but I dont know how can i use stored procedure for replace .No I am able to insert keyword and delete the keywords.But not able to do update function If you have any idea please let me know
Thanks again dude

maeenul
Starting Member

20 Posts

Posted - 2009-04-28 : 00:16:48

Not sure exactly what you wanted to do. Do you want to update the t field when 'A' is passed as action. If you want to update, why dont you use simple update statement?

update myproduct
set t=@param_t
where i = @param_i
and j = @param_j

-----------------------
maeenul
http://www.programmingsolution.net/sqlserver2005/sqlserversolutions/index.php
http://sqlservertipsntricks.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources