February 28, 2005 at 9:58 am
I have a Varchar value in the db like this XYZ.what I want to
do is that anytime someone tries to save XYZ it should check for existence and if found then save it as
XYZ-1 and if i want to save XYZ-1 then it should check first and increment the value and save it as XYZ-2 and so on. How can i do that in SP. I need to check also that
value i am trying to insert exists or not.
Please advise.
February 28, 2005 at 11:09 am
Having a single column containing two pieces of information (the value and the sequence within the value) is a violation of the rules of normalizatoim and will cause great difficulties including difficulty in getting SQL to produce the desired results, sorting and performance. Instead recommend you have 2 columns, one column for the value and a differenct column for the sequence. You may always concatenate the two columns into a single derived column as needed.
create table Foos
( FooName varchar(255) not null
, FooNameSequence integer not null
, constraint Foos_P primary key (FooName , FooNameSequence)
, constraint Foos_C_FooNameSequence check (FooNameSequence >= 0 )
)
declare @FooName varchar(255)
, @TestCnt integer
set @FooName = 'XYZ'
set @TestCnt = 0
WHILE @TestCnt < 20
begin
insert into Foos
(FooName , FooNameSequence )
select @FooName , COALESCE ( MAX(FooNameSequence) + 1 , 0 )
from Foos
where FooName = @FooName
set @TestCnt = @TestCnt + 1
end
select FooName
, FooNameSequence
, FooName + '-' + cast(FooNameSequence as varchar(8) )
from Foos
order by FooName , FooNameSequence
drop table Foos
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply