sp1负责增删改，其它特殊外置sp0，select一般写到代码中

SELECT TOP 1000 [ProductRecommendID]
      ,[FromResourceID]
      ,[ToProductID]
      ,[DataChange_LastTime]
      ,[CreateUser]
      ,[ModifyUser]
      ,[DataChange_CreateTime]
      ,[RecommendDesc]
      ,[FromCategory]

  FROM [HHProductDB].[dbo].[Prd_ProductRecommend]

ALTER PROCEDURE [dbo].[spA_Prd_ProductRecommend_i]
@ProductRecommendID bigint output,
@FromResourceID bigint=NULL,
@ToProductID bigint,
@DataChange_LastTime datetime=NULL,
@CreateUser nvarchar(50)=NULL,
@ModifyUser nvarchar(50)=NULL,
@DataChange_CreateTime datetime=NULL,
@RecommendDesc nvarchar(500),
@FromCategory int
AS


DECLARE @retcode int, @rowcount int


SET LOCK_TIMEOUT 1000


INSERT INTO Prd_ProductRecommend([FromResourceID],[ToProductID],[DataChange_LastTime],[CreateUser],[ModifyUser],[DataChange_CreateTime],[RecommendDesc],[FromCategory])
VALUES(ISNULL(@FromResourceID,(0)),@ToProductID,ISNULL(@DataChange_LastTime,getdate()),ISNULL(@CreateUser,'System'),ISNULL(@ModifyUser,'System'),ISNULL(@DataChange_CreateTime,getdate()),@RecommendDesc,@FromCategory)


SELECT @retcode = @@ERROR, @rowcount = @@ROWCOUNT,@ProductRecommendID=@@IDENTITY
IF @retcode = 0 AND @rowcount = 0
BEGIN
 SELECT 100
 RETURN 100
END
ELSE
BEGIN
 SELECT @retcode
 RETURN @retcode

ALTER PROCEDURE [dbo].[spA_Prd_ProductRecommend_u]
 @ProductRecommendID bigint,
 @FromResourceID bigint=NULL,
 @ToProductID bigint=NULL,
 @DataChange_LastTime datetime=NULL,
 @CreateUser nvarchar(50)=NULL,
 @ModifyUser nvarchar(50)=NULL,
 @DataChange_CreateTime datetime=NULL,
 @RecommendDesc nvarchar(500)=NULL,
 @FromCategory int=NULL
AS


DECLARE @retcode int, @rowcount int


SET LOCK_TIMEOUT 1000


UPDATE Prd_ProductRecommend SET
 [FromResourceID]=ISNULL(@FromResourceID,[FromResourceID]),
 [ToProductID]=ISNULL(@ToProductID,[ToProductID]),
 [DataChange_LastTime]=ISNULL(@DataChange_LastTime,[DataChange_LastTime]),
 [CreateUser]=ISNULL(@CreateUser,[CreateUser]),
 [ModifyUser]=ISNULL(@ModifyUser,[ModifyUser]),
 [DataChange_CreateTime]=ISNULL(@DataChange_CreateTime,[DataChange_CreateTime]),
 [RecommendDesc]=ISNULL(@RecommendDesc,[RecommendDesc]),
 [FromCategory]=ISNULL(@FromCategory,[FromCategory])
WHERE ProductRecommendID=@ProductRecommendID


SELECT @retcode = @@ERROR, @rowcount = @@ROWCOUNT
IF @retcode = 0 AND @rowcount = 0
   BEGIN
 SELECT 100
 RETURN 100
END  
ELSE
BEGIN
 SELECT @retcode
 RETURN @retcode
END  

ALTER PROCEDURE [dbo].[spA_Prd_ProductRecommend_d]
@ProductRecommendID bigint
AS


DECLARE @retcode int, @rowcount int


SET LOCK_TIMEOUT 1000


DELETE Prd_ProductRecommend WHERE [ProductRecommendID]=@ProductRecommendID


SELECT @retcode = @@ERROR, @rowcount = @@ROWCOUNT
IF @retcode = 0 AND @rowcount = 0
BEGIN
 SELECT 100
 RETURN 100
END  
ELSE
BEGIN
 SELECT @retcode
 RETURN @retcode
END