两个简单的存储过程如何合并写成一个。

沐NeMo 2008-04-08 05:31:05
--插入记录。
CREATE PROCEDURE [dbo].[AddFactory]
@ID smallint,
@FactoryName nvarchar(50),
@LinkMan nvarchar(50)='',
@Tel nvarchar(50)='',
@Fax nvarchar(50)='',
@Address nvarchar(255)='',
@Email nvarchar(255)=''
AS
Declare @NewID smallint
If Exists (SELECT * FROM [FactoryInfo] WHERE FactoryName=@FactoryName)
Set @NewID=0
Else
Begin
Select @NewID=IsNull(Max([ID]),0)+1 From [FactoryInfo]
INSERT INTO [FactoryInfo] ([ID],FactoryName,LinkMan,Tel,Fax,Address,Email) Values (@NewID,@FactoryName,@LinkMan,@Tel,@Fax,@Address,@Email)
End
Select @NewID
GO

-----------------------
--更新记录
CREATE PROCEDURE [dbo].[UpdateFactory]
@ID smallint,
@FactoryName nvarchar(50),
@LinkMan nvarchar(50)='',
@Tel nvarchar(50)='',
@Fax nvarchar(50)='',
@Address nvarchar(255)='',
@Email nvarchar(255)=''
AS
Declare @RowCount smallint
Select @OLDFactoryName=FactoryName From [FactoryInfo] Where [ID]=@ID
Update [FactoryInfo] Set FactoryName=@FactoryName,LinkMan=@LinkMan,Tel=@Tel,Fax=@Fax,Address=@Address,Email=@Email Where [ID]=@ID
Set @RowCount=@@RowCount
Select @RowCount
GO
------
用一个存储过程实现,要添加一个工厂信息到数据库里面。如果已经存在就更新,如果没有就插入作为新记录。
如何合并或者优化成一个存储过程。 在调用存储过程时候就可以不用去判断记录是否为新还是已经存在了。少调用一个。
谢谢大家了。
...全文
96 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
沐NeMo 2008-04-10
主要思路都一样。
感觉4楼的最好。插入或更新,都返回:Select @NewID
就采用4楼的。
3楼的if exists(select 1 from [FactoryInfo] where [ID]=@ID )还是第一次见过。直接select 1就可以了。学习了。
回复
conan304 2008-04-08
CREATE PROCEDURE [dbo].[AddFactory] 
@ID smallint,
@FactoryName nvarchar(50),
@LinkMan nvarchar(50)='',
@Tel nvarchar(50)='',
@Fax nvarchar(50)='',
@Address nvarchar(255)='',
@Email nvarchar(255)=''
AS

Declare @NewID smallint

If Exists (SELECT * FROM [FactoryInfo] WHERE FactoryName=@FactoryName)
begin
Declare @RowCount smallint
Select @OLDFactoryName=FactoryName From [FactoryInfo] Where [ID]=@ID

Update [FactoryInfo]
Set FactoryName=@FactoryName,LinkMan=@LinkMan,Tel=@Tel,Fax=@Fax,Address=@Address,Email=@Email
Where [ID]=@ID

Set @RowCount=@@RowCount
Select @RowCount
Set @NewID=0
end
else
Begin
Select @NewID=IsNull(Max([ID]),0)+1 From [FactoryInfo]
INSERT [FactoryInfo]([ID],FactoryName,LinkMan,Tel,Fax,Address,Email)
Values (@NewID,@FactoryName,@LinkMan,@Tel,@Fax,@Address,@Email)
End
Select @NewID

GO
回复
yyyyzzzz_2002 2008-04-08
合并就对了
CREATE PROCEDURE [dbo].[AddFactory]
@ID smallint,
@FactoryName nvarchar(50),
@LinkMan nvarchar(50)='',
@Tel nvarchar(50)='',
@Fax nvarchar(50)='',
@Address nvarchar(255)='',
@Email nvarchar(255)=''
AS

If Exists (SELECT * FROM [FactoryInfo] WHERE FactoryName=@FactoryName)
Begin
Declare @NewID smallint
set @NewID=0
Select @NewID=IsNull(Max([ID]),0)+1 From [FactoryInfo]
INSERT INTO [FactoryInfo] ([ID],FactoryName,LinkMan,Tel,
Fax,Address,Email)
Values (@NewID,@FactoryName,@LinkMan,@Tel,@Fax,@Address,@Email)
End

else

begin
Declare @RowCount smallint
Select @OLDFactoryName=FactoryName From [FactoryInfo] Where [ID]=@ID
Update [FactoryInfo] Set FactoryName=@FactoryName
,LinkMan=@LinkMan,Tel=@Tel,
Fax=@Fax,Address=@Address
,Email=@Email Where [ID]=@ID
Set @RowCount=@@RowCount
Select @RowCount
end

GO
回复
chuifengde 2008-04-08
CREATE PROCEDURE [dbo].[AddFactory] 
@ID smallint,
@FactoryName nvarchar(50),
@LinkMan nvarchar(50)='',
@Tel nvarchar(50)='',
@Fax nvarchar(50)='',
@Address nvarchar(255)='',
@Email nvarchar(255)=''
AS
Declare @NewID smallint
If Exists (SELECT * FROM [FactoryInfo] WHERE FactoryName=@FactoryName)
begin
Update [FactoryInfo] Set FactoryName=@FactoryName,LinkMan=@LinkMan,Tel=@Tel,Fax=@Fax,Address=@Address,Email=@Email Where [ID]=@ID
Set @NewID=0
end
Else
Begin
Select @NewID=IsNull(Max([ID]),0)+1 From [FactoryInfo]
INSERT INTO [FactoryInfo] ([ID],FactoryName,LinkMan,Tel,Fax,Address,Email) Values (@NewID,@FactoryName,@LinkMan,@Tel,@Fax,@Address,@Email)
End
Select @NewID
回复
xiaomeixiang 2008-04-08

-----------------------
--更新记录
CREATE PROCEDURE [dbo].[UpdateFactory]
@ID smallint,
@FactoryName nvarchar(50),
@LinkMan nvarchar(50)='',
@Tel nvarchar(50)='',
@Fax nvarchar(50)='',
@Address nvarchar(255)='',
@Email nvarchar(255)=''
AS

if exists(select 1 from [FactoryInfo] where [ID]=@ID )
begin
Declare @RowCount smallint
Select @FactoryName=FactoryName From [FactoryInfo] Where [ID]=@ID
Update [FactoryInfo] Set FactoryName=@FactoryName,LinkMan=@LinkMan,Tel=@Tel,Fax=@Fax,Address=@Address,Email=@Email Where [ID]=@ID
Set @RowCount=@@RowCount
Select @RowCount
end
else
begin
Declare @NewID smallint
If Exists (SELECT * FROM [FactoryInfo] WHERE FactoryName=@FactoryName)
Set @NewID=0
Else
Begin
Select @NewID=IsNull(Max([ID]),0)+1 From [FactoryInfo]
INSERT INTO [FactoryInfo] ([ID],FactoryName,LinkMan,Tel,Fax,Address,Email) Values (@NewID,@FactoryName,@LinkMan,@Tel,@Fax,@Address,@Email)
End
Select @NewID
end
GO
回复
zccmy22 2008-04-08
--插入记录。
CREATE PROCEDURE [dbo].[AddFactory]
@ID smallint,
@FactoryName nvarchar(50),
@LinkMan nvarchar(50)='',
@Tel nvarchar(50)='',
@Fax nvarchar(50)='',
@Address nvarchar(255)='',
@Email nvarchar(255)='',
@Status char(1) --'i' 插入, 'u'更新
AS
Declare @NewID smallint
if @Status = 'i'
If Exists (SELECT * FROM [FactoryInfo] WHERE FactoryName=@FactoryName)
Set @NewID=0
Else
Begin
Select @NewID=IsNull(Max([ID]),0)+1 From [FactoryInfo]
INSERT INTO [FactoryInfo] ([ID],FactoryName,LinkMan,Tel,Fax,Address,Email) Values (@NewID,@FactoryName,@LinkMan,@Tel,@Fax,@Address,@Email)
End
else if @Status = 'u'
Declare @RowCount smallint
Select @OLDFactoryName=FactoryName From [FactoryInfo] Where [ID]=@ID
Update [FactoryInfo] Set FactoryName=@FactoryName,LinkMan=@LinkMan,Tel=@Tel,Fax=@Fax,Address=@Address,Email=@Email Where [ID]=@ID
Set @RowCount=@@RowCount
Select @RowCount
end
Select @NewID
GO


只是一个思路,应可行的,具体不记得了。我也是新手。
回复
liangCK 2008-04-08
if exists(select * from tb where id=@id)
更新
else
插入
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-08 05:31
社区公告
暂无公告