December 7, 2012 at 3:06 pm
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 here
IF (@Vid = 0)
Begin
Select @VId = VId from tvDetail where EId = @ID
Set @VId = ISNULL(@VId,0)
END
But 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]
GO
SET ANSI_PADDING OFF
GO
****** Here is My Store Procedure******
ALTER Procedure [dbo].[usp_Save]
@ID as int,
@sid as int = '',
@UID as int = ''
as
set nocount on
Declare @EStatus as varchar(100) = ''
Select @EStatus = replace(EStatus,' ','')
from tEStatus
where ESId = @sid
if 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
fromtApp A
inner join tType AT on A.ATypeId = AT.ATId
whereEId = @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
)
End
ELSE
Declare @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 JOINuvw_ETrail et ON ET.EId = @ID)
Where tvDetail .EID = @ID
END
ELSE
IF (@Vid = 0)
Begin
Select @VId = VId from tvDetail where EId = @ID
Set @VId = ISNULL(@VId,0)
END
ELSE
BEGIN
set @StrScript = 'Update tvDetail Set '
+ @EStatus
+ ' = getdate(),ModUserId = '
+ Convert(varchar,@UID)
+ ',ModDate = getdate() where EId = '
+ Convert(varchar,@ID)
Exec dbo.sp_executesql @StrScript
END
And also if any one can guide me how i can optimize this Sp, that would be great.
Thank You in advance.
December 10, 2012 at 4:40 pm
Is the name "IDeleted" on the INSERT statement just a typo for "IsDeleted", as it appears on the table? Or could the mistyped column name be causing you the problem?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 10, 2012 at 9:07 pm
Thank You for Look into it. Yes it is TYPO Mistake...
Please any advise would be great appreciate!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply