关闭

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

427人阅读 评论(0) 收藏 举报
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 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1376363次
    • 积分:19894
    • 等级:
    • 排名:第412名
    • 原创:448篇
    • 转载:1067篇
    • 译文:4篇
    • 评论:143条
    文章存档
    最新评论