April 23, 2009 at 1:42 pm
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
April 23, 2009 at 1:47 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
By the sound of it you have a list of keywords in a text column. Is that correct? If so, I strongly urge you to consider normalising that design. It's not going to be easy to work with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 2:05 pm
Thanks for your reply
Yes exactly.
"If so, I strongly urge you to consider normalising that design". How do I normalise that design??
CREATE TABLE [dbo].[MyTableSearch](
[Myfield] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
April 23, 2009 at 2:10 pm
Sample data?
Desired output?
Regarding normalisation, read these -
http://msdn.microsoft.com/en-us/library/ms191178.aspx
http://en.wikipedia.org/wiki/Database_normalization
http://support.microsoft.com/kb/283878
http://www.databasejournal.com/sqletc/article.php/1428511/Database-Normalization.htm
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 2:12 pm
How exactly to envision the data being used? What are some samples ways this data is being gathered, user for search?
That will help us to determine how the entity lives in the real world.
A short example of normalizing is to put each keyword as a separate row in the table.
April 23, 2009 at 2:28 pm
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.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 ie desired output.
April 23, 2009 at 2:50 pm
Why is all that going into a single text field? Why not have one row per keyword? That'll be a LOT easier to work with.
- 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
April 23, 2009 at 3:00 pm
I stored it as single keyword per each row
eg:'a' in first row
'b' second row etc.could you please let me know abt the solution
April 23, 2009 at 3:15 pm
I think we're talking about two different kinds of rows. I'm talking about rows in the table. You seem to be talking about having a return character in between words in a text field.
Here's what you seem to be describing:
create table #YourVersion (
ID int identity primary key,
KeyWords text);
--
insert into #YourVersion(KeyWords)
select 'FirstKeyWord
SecondKeyWord
ThirdKeyWord';
Here's what I'm talking about:
create table #MyVersion (
ID int identity primary key,
KeyWordSetID int not null,
KeyWord varchar(100));
--
insert into #MyVersion (KeyWordSetID, KeyWord)
select 1, 'FirstKeyWord' union all
select 1, 'SecondKeyWord' union all
select 1, 'ThirdKeyWord';
Run both of the above scripts in Management Studio, then run this:
select *
from #YourVersion;
--
select *
from #MyVersion
You'll see the difference. In my version, instead of having it all in one field, I have one word per row in the table, with a "KeyWordSetID" column that tells me which ones go together with which other ones.
In my version, if I want to add a word to the set, I just insert it into the table.
In your version, if you want to add a word to the set, you have to call on the text datatype editing functions, which are quite complex. You could simplify it a little bit by using varchar(max) instead of text as the datatype for the field. That would make it easier, but still not as easy as my version of the table.
That's what we mean by "normalize" the data. Does that help? Do you understand now?
- 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
April 24, 2009 at 10:33 am
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-2 int ,
@t varchar(8000) ,
@Action varchar(1)-- 'I insert, A append
as
declare @ptr binary(16)
if @Action = 'I'
begin
insertmySecond
(
i ,
j ,
t
)
select @i ,
@j-2 ,
@t
end
if @Action = 'A'
begin
select @ptr = textptr(t)
frommySecond
wherei = @i
andj = @j-2
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-2 varchar(8000)
select @s-2 = replicate('a',6000)
exec nr_AddText 2, 1, @s-2, 'I'
exec nr_AddText 2, 1, @s-2, 'A'
exec nr_AddText 2, 1, 'jjjjj', 'A'
exec nr_AddText 2, 1, 'kkkkk', 'A'
exec nr_AddText 2, 1, @s-2, '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
April 24, 2009 at 10:37 am
Please post some existing sample data from the table. The very first link that I gave you shows how to do that easily.
Why can't you change the table structure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2009 at 11:16 am
If you're just trying to make a comma list out of the vertical list, try the Replace function. It can replace a line-break with a comma pretty easily.
- 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
April 24, 2009 at 3:05 pm
Hi Guys
Thanks for your reply
I used stored procedure for inserting keywords into a text data filed.Now I need to do update function
My code is as below Please let me know if u have any idea
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-2 int ,
@t varchar(8000) ,
@Action varchar(1)-- 'I insert
as
declare @ptr binary(16)
if @Action = 'I'
begin
insertmyProduct
(
i ,
j ,
t
)
select @i ,
@j-2 ,
@t
end
if @Action = 'A'
begin
select @ptr = textptr(t)
frommySecond
wherei = @i
andj = @j-2
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'
April 25, 2009 at 3:27 am
GilaMonster (4/24/2009)
Please post some existing sample data from the table. The very first link that I gave you shows how to do that easily.Why can't you change the table structure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply