Store procedure

• raghunandansharma12

  SSC Rookie

  Points: 48

  More actions

  February 4, 2008 at 12:35 am

  #93029

  Plz Help me

  I have a store procedure

  Problem is that i have three table class,classSubject_Relation and Subject........

  class table consist column cls_id(pk),cls_name

  class_subject table cloumn cls_id(fk),sub_id(fk)

  subject table column sub_id(pk),sub_name

  How can i store differnt subject with one classId where cls_id is Auto-gentrated(identity)

  i want data is stored in database like

  in class table

  cls_id cls_name

  1 MCA

  In relational table

  cls_id(fk) sub_id(fk)

  1 1

  1 2

  1 3

  Here @sub_id contains string such as @sub_id=('1,2,3,)

  set ANSI_NULLS ON

  set QUOTED_IDENTIFIER ON

  GO

  ALTER PROCEDURE [dbo].[insertvalues]

  @cls_name nvarchar(50),

  @cls_strength int,

  @grp_id int,

  @sub_id nvarchar(50)

  AS

  BEGIN

  DECLARE @pos int,

  @nextpos int ,

  @valuelen int

  SELECT @pos = 0, @nextpos = 1

  WHILE @nextpos > 0

  BEGIN

  SELECT @nextpos = charindex(',', @sub_id, @pos + 1)

  SELECT @valuelen = CASE WHEN @nextpos > 0

  THEN @nextpos

  ELSE len(@sub_id) + 1

  END - @pos - 1

  --insert into class_subject(cls_id,sub_id) values (@@identity,@sub_id)

  INSERT into class_subject(cls_id,sub_id)

  VALUES(@@identity,(convert(int, substring(@sub_id, @pos + 1, @valuelen))))

  SELECT @pos = @nextpos

  select @@identity

  END

  RETURN

  insert into class(cls_name,cls_strength,grp_id) values(@cls_name,@cls_strength,@grp_id)

  end

  Raghu

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 4, 2008 at 1:11 am

  #774654

  Please don't cross post. It's not necessary, the regular's read all the forums. It also fragments replies and wastes people's time.

  Discussion to the following thread please - http://www.sqlservercentral.com/Forums/Topic451025-338-1.aspx

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass