Need Help With this SQL...

• nerbeans

  Grasshopper

  Points: 11

  More actions

  February 28, 2005 at 9:58 am

  #362090

  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.

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  February 28, 2005 at 11:09 am

  #544466

  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