SP Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SP Help

Author

Topic

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 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
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
)
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 JOIN uvw_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.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-08 : 13:14:29

Please read this

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources