SP Help...

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » SP Help...

SP Help...

Rate Topic

Display Mode

Topic Options

Author

Message

rocky_498

Posted Friday, December 07, 2012 3:06 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:46 PM
Points: 149, Visits: 546

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.

Post #1394259

ScottPletcher

Posted Monday, December 10, 2012 4:40 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1394816

rocky_498

Posted Monday, December 10, 2012 9:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:46 PM
Points: 149, Visits: 546

Thank You for Look into it. Yes it is TYPO Mistake...
Please any advise would be great appreciate!

Post #1394867

« Prev Topic | Next Topic »

Permissions