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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SP Help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

202 Posts

Posted - 12/07/2012 :  17:05:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/08/2012 :  13:14:29  Show Profile  Reply with Quote
Please read this

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000