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-23 : 16:49:33

Hi
I am new to stored procedures.I have a table with one text field .I need to create a stored procedure for addingand deleting keywords in that text filed.Also I need show all the records of keywords in that text field

My table definition:

CREATE TABLE [dbo].[MyTableSearch](
[Myfield] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

sample data:In my text data field I am using sample data as 'a' , 'b' ,'c' etc.Each of this key word is stored in seperate rows

Desired output:I need to create a stored procedure for inserting new keyword to that field such as 'e', 'f' like that.Also I need to delete these values and show all the keywords

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-23 : 17:46:54

Have a look at
http://www.nigelrivett.net/SQLTsql/ReplaceText.html
http://www.nigelrivett.net/SQLTsql/InsertTextData.html

Why are you using a text column rather than varchar(max)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqlstarter1
Starting Member

6 Posts

Posted - 2009-04-24 : 12:19:40

These links are helpful.But I need to store the keywords 'a','b', 'c' in the same row of nvarchar(max) data field.I need to get the list of keyword from the same rowusing the stored procedure.I thought that It is possible to normalise the keywords.but I need the keywords 'a','b','c' etc in the same row.
ie I need to get a list of keyword seperated by comas as the result

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-24 : 13:03:59

I'm a little confused about what you are trying to do. You mention that you have data like 'a' and 'b' in separate rows, presumably in the MyTableSearch table. Then in your post @ 12:19:40 you mention storing the keywords in the same row.

What version of SQL are you using and which columns are TEXT or NVARCHAR(MAX) (you mentioned both)?

A better solution in to normalize your data: [url]http://www.datamodel.org/NormalizationRules.html[/url]

Assuming you still want to continue with this design, can give us more detail about what the data looks like (sample data with insert statements)? Also, how is the data to be manipulated? Are you passing string data to your stored procedure or just selected everything from your table and concatenating it together?

sqlstarter1
Starting Member

6 Posts

Posted - 2009-04-24 : 13:14:02

I used my text field as nvarch ar(max).But I need a list of keywords in the same row separated by comas.Now I understand about normalisation.But unfortunately I am not able to normalise my table.
I used a stored proc.But It does not work for getting the list of keywords
my code for stored proc as below
Create table mySecond (i int, j int, t text)
go
create unique index ix on mySecond
(i, j)
go

create proc nr_AddText
@i int ,
@j int ,
@t varchar(8000) ,
@Action varchar(1) -- 'I insert, A append
as
declare @ptr binary(16)

if @Action = 'I'
begin
insert mySecond
(
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 mySecond.t @ptr null 0 @t
end

go

exec nr_AddText 1, 1, 'asadf', 'I'
exec nr_AddText 1, 1, 'jjjjj', 'A'
exec nr_AddText 1, 1, 'kkkkk', 'A'
declare @s varchar(8000)
select @s = replicate('a',6000)
exec nr_AddText 2, 1, @s, 'I'
exec nr_AddText 2, 1, @s, 'A'
exec nr_AddText 2, 1, 'jjjjj', 'A'
exec nr_AddText 2, 1, 'kkkkk', 'A'
exec nr_AddText 2, 1, @s, 'A'

select i,j, substring(t, 1, 8000) from mySecond
select i,j, substring(t, 8001, 8000) from mySecond
select i,j, substring(t, 16001, 8000) from mySecond
------------------------------------------------------------------------------------

how can i modify this stored pocedure for getting list of keywords in same row seperated by comas

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-24 : 13:53:54

So if I select the first row I get a string like: asadfjjjjjkkkkk

What do you want as the output? a,s,a,d,f,j,j,j,j,j,k,k,k,k,k ??

sqlstarter1
Starting Member

6 Posts

Posted - 2009-04-24 : 14:07:27

'a' ,'b', 'c','d'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-24 : 15:08:00

If I understand you correctly, then you could either create a function to modify the string or add something like:

DECLARE @String VARCHAR(MAX)
DECLARE @OutString VARCHAR(MAX)
DECLARE @Index INT

SELECT @String = SUBSTRING(t, 1, 2147483647) 
FROM mySecond WHERE i = @i and j = @j 

SET @Index = 1

WHILE COALESCE(LEN(@String), 0) >= @Index
BEGIN
	SET @OutString = COALESCE(@OutString + ',' , '') + CHAR(39) + SUBSTRING(@String, @Index, 1) + CHAR(39)
	SET @Index = @Index + 1
END

SELECT @OutString

EDIT: forgot to replace hard coded vales with the variable names from the stored proc.

sqlstarter1
Starting Member

6 Posts

Posted - 2009-04-24 : 16:28:41

Thanks alot for your support and time Lamprey
I used my code as below
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources