ALTER PROCEDURE [dbo].[sp1_Area_CityLandmark_u]
@CityLandmarkID int,
@CnName varchar(100)=NULL,
@CnAbbreviation varchar(100)=NULL,
@Big5Name varchar(100)=NULL,
@Big5Abbreviation varchar(100)=NULL,
@EnName varchar(100)=NULL,
@EnAbbreviation varchar(100)=NULL,
@PinYin varchar(100)=NULL,
@PinYinAbbreviation varchar(100)=NULL,
@Longitude float=NULL,
@Latitude float=NULL,
@CityLandmarkTypeID smallint=NULL,
@CityID int=NULL,
@IsAvailable bit=NULL
AS
DECLARE @retcode int, @rowcount int
SET LOCK_TIMEOUT 1000
UPDATE Area_CityLandmark SET
[CnName]=ISNULL(@CnName,[CnName]),
[CnAbbreviation]=ISNULL(@CnAbbreviation,[CnAbbreviation]),
[Big5Name]=ISNULL(@Big5Name,[Big5Name]),
[Big5Abbreviation]=ISNULL(@Big5Abbreviation,[Big5Abbreviation]),
[EnName]=ISNULL(@EnName,[EnName]),
[EnAbbreviation]=ISNULL(@EnAbbreviation,[EnAbbreviation]),
[PinYin]=ISNULL(@PinYin,[PinYin]),
[PinYinAbbreviation]=ISNULL(@PinYinAbbreviation,[PinYinAbbreviation]),
[Longitude]=ISNULL(@Longitude,[Longitude]),
[Latitude]=ISNULL(@Latitude,[Latitude]),
[CityLandmarkTypeID]=ISNULL(@CityLandmarkTypeID,[CityLandmarkTypeID]),
[CityID]=ISNULL(@CityID,[CityID]),
[IsAvailable]=ISNULL(@IsAvailable,[IsAvailable])
WHERE CityLandmarkID=@CityLandmarkID
SELECT @retcode = @@ERROR, @rowcount = @@ROWCOUNT
IF @retcode = 0 AND @rowcount = 0
RETURN 100
ELSE
RETURN @retcode
ALTER PROCEDURE [dbo].[sp1_Area_CityLandmark_i]
@CityLandmarkID int output,
@CnName varchar(100),
@CnAbbreviation varchar(100)=NULL,
@Big5Name varchar(100)=NULL,
@Big5Abbreviation varchar(100)=NULL,
@EnName varchar(100)=NULL,
@EnAbbreviation varchar(100)=NULL,
@PinYin varchar(100)=NULL,
@PinYinAbbreviation varchar(100)=NULL,
@Longitude float,
@Latitude float,
@CityLandmarkTypeID smallint=NULL,
@CityID int,
@IsAvailable bit
AS
DECLARE @retcode int, @rowcount int
SET LOCK_TIMEOUT 1000
INSERT INTO Area_CityLandmark([CnName],[CnAbbreviation],[Big5Name],[Big5Abbreviation],[EnName],[EnAbbreviation],[PinYin],[PinYinAbbreviation],[Longitude],[Latitude],[CityLandmarkTypeID],[CityID],[IsAvailable])
VALUES(@CnName,@CnAbbreviation,@Big5Name,@Big5Abbreviation,@EnName,@EnAbbreviation,@PinYin,@PinYinAbbreviation,@Longitude,@Latitude,@CityLandmarkTypeID,@CityID,@IsAvailable)
SELECT @retcode = @@ERROR, @rowcount = @@ROWCOUNT,@CityLandmarkID=@@IDENTITY
IF @retcode = 0 AND @rowcount = 0
RETURN 100
ELSE
RETURN @retcode
ALTER PROCEDURE [dbo].[sp1_Area_CityLandmark_d]
@CityLandmarkID int
AS
DECLARE @retcode int, @rowcount int
SET LOCK_TIMEOUT 1000
DELETE Area_CityLandmark WHERE [CityLandmarkID]=@CityLandmarkID
SELECT @retcode = @@ERROR, @rowcount = @@ROWCOUNT
IF @retcode = 0 AND @rowcount = 0
RETURN 100
ELSE
RETURN @retcode
增删改sp1，系统生成。
其它全部使用sp0，select写到程序中，或写到sp中，这样安全控制得比较好。