Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-12-07 : 17:05:58
|
Below is the SP that i am using in different SP to Populate table ([tVDetail]) that has more than 15 columns. When i execute First store procedure Table Populated with related Sp fine.Know when second store procedure runs they create a other row (It shouldn't be)It should be update only those rows that related with the SP. I am using some logic hereIF (@Vid = 0)Begin Select @VId = VId from tvDetail where EId = @IDSet @VId = ISNULL(@VId,0) ENDBut its still giving me dup value.I am not sure where should i put this logic. Here is my table structure...CREATE TABLE [dbo].[tVDetail]( [VId] [int] IDENTITY(1,1) NOT NULL, [EId] [int] NULL, [PId] [int] NULL, [MId] [int] NULL, [FId] [int] NULL, [DID] [int] NULL, [PrId] [int] NULL, [AId] [int] NULL, [PaId] [int] NULL, [ATime] [datetime] NULL, [AType] [varchar](50) NULL, [PInfo] [varchar](1000) NULL, [Reg] [datetime] NULL, [Roed] [datetime] NULL, [Room] [varchar](25) NULL, [AComplete] [datetime] NULL, [NPending] [datetime] NULL, [DNurse] [datetime] NULL, [Discharged] [datetime] NULL, [SAdded] [datetime] NULL, [TSaved] [datetime] NULL, [NFinalize] [datetime] NULL, [FComplete] [datetime] NULL, [NCoSign] [datetime] NULL, [CComplete] [datetime] NULL, [AAdded] [datetime] NULL, [AUpdated] [datetime] NULL, [SUpdated] [datetime] NULL, [NCompleted] [datetime] NULL, [NOrdered] [datetime] NULL, [DRequest] [datetime] NULL, [DAddendum] [datetime] NULL, [AdComplete] [datetime] NULL, [AoSign] [datetime] NULL, [AddComplete] [datetime] NULL, [AUpdate] [datetime] NULL, [DCM] [datetime] NULL, [CreatedUId] [int] NULL, [CreatedDate] [datetime] NULL, [ModUserId] [int] NULL, [ModDate] [datetime] NULL, [IsDeleted] [bit] NULL, CONSTRAINT [PK__tVDetail__4D3AA1DE02FCD7A7] PRIMARY KEY CLUSTERED ( [VId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO****** Here is My Store Procedure******ALTER Procedure [dbo].[usp_Save]@ID as int,@SID as int = '',@UID as int = ''asset nocount onDeclare @EStatus as varchar(100) = ''Select @EStatus = replace(EStatus,' ','') from tEStatus where ESId = @SIDif not exists (select top 1 1 from tVDetail where EId = @ID)Begin Declare @PInfo as varchar(1000) = '' Declare @PId as int = 0 Declare @MId as int = 0 Declare @FId as int = 0 Declare @DId as int = 0 Declare @PRId as int = 0 Declare @PAId as int = 0 Declare @AId as int = 0 Declare @AType as varchar(250) = '' Declare @ATime as datetime = getdate() Declare @Room as varchar(50) = '' DECLARE @Vid AS INT = 0 Select @PInfo = E.Pinfo, @PId = PId, @MId = PM.MId, @FId= E.FId, @DId= E.DId, @PRId= E.PId from TENC E inner join tSub PS on E.PId = PS.PId inner join tMaster PM on PM.MId = PM.MId where EId = @ID Select @ATime = A.ADate, @AType = AT.ATypeName, @AId = A.AId from tApp A inner join tType AT on A.ATypeId = AT.ATId where EId = @ID if(@AId = 0) Begin Set @AId = null Set @ATime = null Set @AType = null End insert into tEDetail ( EId, PId, MId, FId, DId, PId, AId, ATime, AType, PInfo, CreateId, CreatedDate, ModUserId, ModDate, IDeleted ) Values ( @ID, @PId, @MId, @FId, @DId, @PId, @AId, @ATime, @AType, @PInfo, @UID, GETDATE(), @UID, GETDATE(), 0 )EndELSEDeclare @StrScript as nvarchar(max) = '' if(@SID = 2)Begin Update tvDetail Set Roomed = getdate(), ModUserId = @UID, ModDate = getdate() where EId = @ID UPDATE tvDetail Set Room = (SELECT TOP 1 'Room'+' '+isnull(e.room,0) from TENC e join tTrail ET on et.EId = @ID) Where tvDetail .EID = @ID UPDATE tvDetail Set PId = (SELECT TOP 1 Pid from TENC E INNER JOIN uvw_ETrail et ON ET.EId = @ID) Where tvDetail .EID = @ID ENDELSEIF (@Vid = 0)Begin Select @VId = VId from tvDetail where EId = @IDSet @VId = ISNULL(@VId,0) ENDELSEBEGIN set @StrScript = 'Update tvDetail Set ' + @EStatus + ' = getdate(),ModUserId = ' + Convert(varchar,@UID) + ',ModDate = getdate() where EId = ' + Convert(varchar,@ID) Exec dbo.sp_executesql @StrScriptENDAnd also if any one can guide me how i can optimize this Sp, that would be great.Thank You in advance. |
|