debugging update and insert stored proc.

• John Trevor

  Grasshopper

  Points: 13

  More actions

  April 21, 2008 at 10:44 pm

  #181557

  I need one proc which has to do an update/insert/delete in two tables as per the requirement mentioned below.

  I have tried several combinations of insert etc. but that doesn't work.

  Tables:

  =======

  tFeatureAccess

  tModuleMainAccess

  Table Scripts:

  ==============

  CREATE TABLE tFeatureAccess(intModuleId INT,intFeatureId INT,intAccessLevelId INT,intRoleId INT,strRequestStatus VARCHAR(20), strId VARCHAR(20),strVersionAliasId VARCHAR(20))

  create clustered index temp_clustered_index1 on tFeatureAccess(strId,intRoleId,intFeatureId,intModuleId,intAccessLevelId,strVersionAliasId)

  Note : strRequestStatus can be only either 'A' or 'P'

  CREATE TABLE tModuleMainAccess(strId VARCHAR(20),intAccessLevelId INT,intModuleId INT,strVersionAliasId VARCHAR(20),strIsApproved VARCHAR(20))

  create clustered index temp_clustered_index2 on tModuleMainAccess(strId,intAccessLevelId,intModuleId,strVersionAliasId)

  Note : strIsApproved can be either '1' or '0' I have data coming in from a XML string and after parsing it, I am storing it in a temp table called #temp.

  The values are as below:

  CREATE TABLE #TEMP (ModuleId INT, FeatureID INT, Access INT, MainAccessLevelID INT, IsApproved VARCHAR(20))

  INSERT INTO #TEMP

  VALUES(5,11,3,5,0)

  INSERT INTO #TEMP

  VALUES(5,12,3,5,0)

  INSERT INTO #TEMP

  VALUES(5,13,3,5,0)

  INSERT INTO #TEMP

  VALUES(5,43,3,5,0)

  INSERT INTO #TEMP

  VALUES(2,5,1,6,0)

  INSERT INTO #TEMP

  VALUES(2,6,3,6,0)

  There is no PK on this temp table.

  Note : ModuleId corresponds to intModuleId, FeatureID corresponds to intFeatureId, Access corresponds to intAccessLevelId,MainAccessLevelID corresponds to intRoleId and IsApproved corresponds to strIsApproved

  Here is the requirement:

  ========================

  Input Parameters : @strId ,@strVersionAliasId, XML string containing values for intModuleId,intFeatureId,intAccessLevelId,intRoleId,strRequestStatus. The XML string values are then parsed and stored in the temp table.

  PART I : For table tFeatureAccess

  (a) For a given strId ,strVersionAliasId and strRequestStatus and if some changes are there in tFeatureAccess for intAccessLevelId,intRoleId from what we have in the temp table then update them and change the strRequestStatus from 'P' to 'A'

  (b) If the strRequestStatus is 'A' in tFeatureAccess when comparing to the values in the temp table for intModuleId,intFeatureId, then simply update the rows for intAccessLevelId,intRoleId.

  (c) else if ModuleId,FeatureId available in the temp table does not match with intModuleId,intFeatureId in tFeatureAccess then insert into tFeatureAccess with strRequestStatus as 'A'

  PART II : For table tModuleMainAccess Note this is same as above, but instead of strRequestStatus we have strIsApproved and can be either '1' or '0'.

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  April 22, 2008 at 1:57 pm

  #805740

  Can you show us what you already have?

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]