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

[沐NeMo]

--插入记录。
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
------
用一个存储过程实现，要添加一个工厂信息到数据库里面。如果已经存在就更新，如果没有就插入作为新记录。
如何合并或者优化成一个存储过程。 在调用存储过程时候就可以不用去判断记录是否为新还是已经存在了。少调用一个。
谢谢大家了。

[liangCK]

if exists(select * from tb where id=@id)
更新
else
插入

[zccmy22]

--插入记录。
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


只是一个思路，应可行的，具体不记得了。我也是新手。

[xiaomeixiang]

----------------------- 
--更新记录 
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 

[chuifengde]

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 

[yyyyzzzz_2002]

合并就对了
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 

[conan304]

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

[沐NeMo]

主要思路都一样。
感觉4楼的最好。插入或更新，都返回:Select @NewID
就采用4楼的。
3楼的if exists(select 1 from [FactoryInfo] where [ID]=@ID )还是第一次见过。直接select 1就可以了。学习了。