CREATE TABLE [dbo].[t_user](
[vID] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_t_user_vID] DEFAULT (''),
[vName] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_t_user_vName] DEFAULT (''),
[vGrade] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_t_user_vGrade] DEFAULT (''),
[dBrithday] [smalldatetime] NOT NULL,
[bSex] [bit] NOT NULL CONSTRAINT [DF_t_user_bSex] DEFAULT ((0)),
[vReMark] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_t_user_iSum] DEFAULT ((0))
) ON [PRIMARY]CREATE TABLE [dbo].[t_details](
[iAutoID] [int] IDENTITY(1,1) NOT NULL,
[vID] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_t_details_vID] DEFAULT (''),
[vChinese] [float] NULL CONSTRAINT [DF_t_details_vChinese] DEFAULT ((0)),
[vMaths] [float] NULL CONSTRAINT [DF_t_details_vMaths] DEFAULT ((0)),
[vEnglish] [float] NULL CONSTRAINT [DF_t_details_vEnglish] DEFAULT ((0)),
CONSTRAINT [PK_t_details] PRIMARY KEY CLUSTERED
(
[iAutoID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]alter PROCEDURE Updat_tb @Is_Insert int,
@vID varchar(20),
@vName2 varchar(20),
@vGrade varchar(10),
@Dbrithday varchar(20) ,
@bSex bit,
@dChinese float,
@dMaths float,
@dEnglish float,
@vRe varchar(200)
as
if @Is_Insert =1
begin
INSERT INTO [t_user]([vID],[vName],[vGrade],[dBrithday],[bSex],[vReMark])
VALUES(@vID,@vName2,@vGrade,@Dbrithday,@bSex,@vRe)
INSERT INTO [t_details]([vID],[vChinese],[vMaths],[vEnglish])
VALUES(@vID, @dChinese,@dMaths,@dEnglish)
end
Else
Delete [t_user] where [vID] =@vID
Delete [t_details] where [vID] =@vID
INSERT INTO [t_user]([vID],[vName],[vGrade],[dBrithday],[bSex],[vReMark])
VALUES(@vID,@vName2,@vGrade,@Dbrithday,@bSex,@vRe)
INSERT INTO [t_details]([vID],[vChinese],[vMaths],[vEnglish])
VALUES(@vID, @dChinese,@dMaths,@dEnglish) 我执行 exec Updat_tb 1,'aaa','b','mm','02/23/2011',1,80,90.5,70,'aaaaa'
为什么在[t_details]表中会插入两条相同的记录..
应该在[t_details]表中只会插入一条记录..
[vID] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_t_user_vID] DEFAULT (''),
[vName] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_t_user_vName] DEFAULT (''),
[vGrade] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_t_user_vGrade] DEFAULT (''),
[dBrithday] [smalldatetime] NOT NULL,
[bSex] [bit] NOT NULL CONSTRAINT [DF_t_user_bSex] DEFAULT ((0)),
[vReMark] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_t_user_iSum] DEFAULT ((0))
) ON [PRIMARY]CREATE TABLE [dbo].[t_details](
[iAutoID] [int] IDENTITY(1,1) NOT NULL,
[vID] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_t_details_vID] DEFAULT (''),
[vChinese] [float] NULL CONSTRAINT [DF_t_details_vChinese] DEFAULT ((0)),
[vMaths] [float] NULL CONSTRAINT [DF_t_details_vMaths] DEFAULT ((0)),
[vEnglish] [float] NULL CONSTRAINT [DF_t_details_vEnglish] DEFAULT ((0)),
CONSTRAINT [PK_t_details] PRIMARY KEY CLUSTERED
(
[iAutoID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]alter PROCEDURE Updat_tb @Is_Insert int,
@vID varchar(20),
@vName2 varchar(20),
@vGrade varchar(10),
@Dbrithday varchar(20) ,
@bSex bit,
@dChinese float,
@dMaths float,
@dEnglish float,
@vRe varchar(200)
as
if @Is_Insert =1
begin
INSERT INTO [t_user]([vID],[vName],[vGrade],[dBrithday],[bSex],[vReMark])
VALUES(@vID,@vName2,@vGrade,@Dbrithday,@bSex,@vRe)
INSERT INTO [t_details]([vID],[vChinese],[vMaths],[vEnglish])
VALUES(@vID, @dChinese,@dMaths,@dEnglish)
end
Else
Delete [t_user] where [vID] =@vID
Delete [t_details] where [vID] =@vID
INSERT INTO [t_user]([vID],[vName],[vGrade],[dBrithday],[bSex],[vReMark])
VALUES(@vID,@vName2,@vGrade,@Dbrithday,@bSex,@vRe)
INSERT INTO [t_details]([vID],[vChinese],[vMaths],[vEnglish])
VALUES(@vID, @dChinese,@dMaths,@dEnglish) 我执行 exec Updat_tb 1,'aaa','b','mm','02/23/2011',1,80,90.5,70,'aaaaa'
为什么在[t_details]表中会插入两条相同的记录..
应该在[t_details]表中只会插入一条记录..
--加begin end
Else
begin
Delete [t_user] where [vID] =@vID
Delete [t_details] where [vID] =@vID
INSERT INTO [t_user]([vID],[vName],[vGrade],[dBrithday],[bSex],[vReMark])
VALUES(@vID,@vName2,@vGrade,@Dbrithday,@bSex,@vRe)
INSERT INTO [t_details]([vID],[vChinese],[vMaths],[vEnglish])
VALUES(@vID, @dChinese,@dMaths,@dEnglish)
end
Else
begin
Delete [t_user] where [vID] =@vID
Delete [t_details] where [vID] =@vID
INSERT INTO [t_user]([vID],[vName],[vGrade],[dBrithday],[bSex],[vReMark])
VALUES(@vID,@vName2,@vGrade,@Dbrithday,@bSex,@vRe)
INSERT INTO [t_details]([vID],[vChinese],[vMaths],[vEnglish])
VALUES(@vID, @dChinese,@dMaths,@dEnglish)
end else
如果没加入beginend来控制范围的话,默认只有一句代码是else的字句,你这里也就是else
begin
Delete [t_user] where [vID] =@vID
end
Delete [t_details] where [vID] =@vID
INSERT INTO [t_user]([vID],[vName],[vGrade],[dBrithday],[bSex],[vReMark])
VALUES(@vID,@vName2,@vGrade,@Dbrithday,@bSex,@vRe)
INSERT INTO [t_details]([vID],[vChinese],[vMaths],[vEnglish])
VALUES(@vID, @dChinese,@dMaths,@dEnglish)
所以后面的语句无论你判断结果是什么都会执行的