CREATE Procedure DeleteProduct
(
@id varchar(100),@userid int
)
as
begin
 exec('delete from yx_vote where yx_voidtype=1 and yx_selluser='+@userid+' and yx_proid in('+@id+')')  /*删除留言表*/
 exec('delete from yx_paipai where yx_userid='+@userid+' and  yx_proid in ('+@id+')')  /*删除竞价表*/
 exec('delete from yx_orderform where  yx_userid='+@userid+' and yx_proid in  ('+@id+')')  /*删除订单表*/
 exec('delete from yx_product where  yx_userid='+@userid+' and yx_id in ('+@id+')')  /*删除商品表*/
end


~

CREATE Procedure DeleteUser  /*删除用户*/
(
@id varchar(100)
)
as
begin
 exec('delete from YX_UserInfo where YX_UserID='+@id+'')  /*删除会员*/
 exec('delete from YX_Shop where yx_shopuserid='+@id+'')  /*删除商铺*/
 exec('delete from YX_Product where yx_userid='+@id+'')  /*删除商品*/
 exec('delete from YX_ts where YX_userid='+@id+'')  /*删除投述表*/
 exec('delete from YX_ts where YX_tsuserid='+@id+'')  /*删除投述表*/
 exec('delete from YX_OrderForm where YX_userid='+@id+'')  /*删除订单表*/
 exec('delete from YX_OrderForm where YX_reuserid='+@id+'')  /*删除订单表*/
 exec('delete from YX_SHOPClass where YX_userid='+@id+'')  /*删除用户自定义分类表*/
 exec('delete from YX_commend where YX_userid='+@id+'')  /*删除推荐商品表*/
 exec('delete from YX_Link where YX_linkUserID='+@id+'')  /*删除友情链接表*/
 exec('delete from YX_Link where YX_Userid='+@id+'')  /*删除友情链接表*/
 exec('delete from YX_Onlinepayment where yx_userid='+@id+'')  /*删除支付宝邮箱表*/
 exec('delete from YX_paipai where yx_userid='+@id+'')  /*删除拍买临时表*/
 exec('delete from YX_paipai where YX_reuserid='+@id+'')  /*删除拍买临时表*/
 exec('delete from YX_Collection where YX_CollUser='+@id+'')  /*删除收藏商品表*/
 exec('delete from Yx_Messages where Yx_SendID='+@id+'')  /*删除信息留言表*/
 exec('delete from Yx_Messages where Yx_GetID='+@id+'')  /*删除信息留言表*/
 exec('delete from YX_Promanger where YX_userid='+@id+'')  /*删除收货地址表*/
 exec('delete from YX_sell where YX_userid='+@id+'')  /*删除评价表*/
 exec('delete from YX_sell where YX_selluserid='+@id+'')  /*删除评价表*/

end



~


create Procedure DownClass
(
@YX_ID int
)
as
declare @F_ParentID varchar(100)/*父级分类*/
declare @F_Path varchar(100)/*父级分类路径*/
declare @F_Child int/*父级分类的Child*/
declare @F_PrevID int/*父级分类的同深度分类上个ID*/
declare @F_NextID int/*父级分类的同深度分类下个ID*/
declare @F_OrderID int /*父级分类的排序ID*/
declare @TempPath varchar(100) /*临时路径*/
declare @TempNum int /*临时int型*/
select  @F_ParentID=YX_ParentID,@F_Path=YX_ParentPath,@F_Child=YX_Child,@F_PrevID=YX_PrevID,@F_NextID=YX_NextID
,@F_OrderID=YX_OrderID from YX_productclass where YX_ID=@YX_ID
	
	if @F_NextID>0
        begin
        	exec UpClass @F_NextID		
	end


~


create Procedure DownNewsClass
(
@YX_ID int
)
as
declare @F_ParentID varchar(100)/*父级分类*/
declare @F_Path varchar(100)/*父级分类路径*/
declare @F_Child int/*父级分类的Child*/
declare @F_PrevID int/*父级分类的同深度分类上个ID*/
declare @F_NextID int/*父级分类的同深度分类下个ID*/
declare @F_OrderID int /*父级分类的排序ID*/
declare @TempPath varchar(100) /*临时路径*/
declare @TempNum int /*临时int型*/
select  @F_ParentID=YX_ParentID,@F_Path=YX_ParentPath,@F_Child=YX_Child,@F_PrevID=YX_PrevID,@F_NextID=YX_NextID
,@F_OrderID=YX_OrderID from YX_NewsClass where YX_ID=@YX_ID
	
	if @F_NextID>0
        begin
        	exec UpNewsClass @F_NextID		
	end



~


CREATE Procedure GetMentList

/*查询出文章频道列表*/
@TempID int
as
select * from YX_Channel where YX_Template=@TempID


~

create Procedure Pro_ArticleTemplte
(
@YX_CMSModelName varchar(50),
@ChannelID int,
@YX_TemplateType int
)
as
select * from YX_CMSModel where YX_CMSModelName=@YX_CMSModelName and YX_TemplateType=@YX_TemplateType and ChannelID=@ChannelID




~


/*根据条件返回一列数据*/
CREATE Procedure Pro_GetObj(
@tab varchar(50) ,/*表名*/
@colunm varchar(50),/*要查询的列*/
@where_colunm varchar(50),/*判断条件*/
@wh_value varchar(50),/*判断条件值*/
@numb int /*判断是数字还是字符【1】数字【0】字符*/
)
as
begin
declare @Sqlstr varchar(500)
if @numb=0 /*条件是字符*/
	begin
	set @Sqlstr='select '+@colunm+' from ['+@tab+'] where '+@where_colunm+' = '''+@wh_value+''''
	end
else
	begin
	set @Sqlstr='select '+@colunm+' from ['+@tab+'] where '+@where_colunm+' = '+@wh_value+''
	end

exec(@Sqlstr)

end


~


create Procedure Pro_GetTemplate

(
@modename varchar(50),
@TemplateID int
)
as
select * from YX_CMSModel where YX_CMSModelName=@modename and YX_TemplateType=@TemplateID


~


create Procedure Pro_Select
(
@YX_sqlstr varchar(8000)
)
as
exec(@YX_sqlstr)


~


Create Procedure Pro_ShopProClass
(
@Uid int
)
as
begin
 select * from YX_ShopClass where YX_Userid=@Uid
end


~


CREATE Procedure Pro_ShowProduct
(
@id int
)
as
select * from YX_Product where YX_ID=@id 


~


create proc Pro_UpdateShopbase_pic
(
@YX_ShopName varchar(50),
@YX_ShopClass int,
@YX_Shoppic varchar(50),
@YX_ShopProject varchar(60),
@YX_ShopContent varchar(3000),
@YX_SHOPUserID int

)
 as
update YX_Shop set YX_ShopName=@YX_ShopName,YX_ShopClass=@YX_ShopClass,YX_SHOPLogo=@YX_Shoppic ,YX_ShopProject=@YX_ShopProject,YX_ShopContent=@YX_ShopContent where YX_SHOPUserID=@YX_SHOPUserID


~


CREATE Procedure Pro_deleteArticleCMSclass
(
@id int
)
as 
update YX_Article_CMSClass set yx_nextid=(select yx_nextid from YX_NewsClass where yx_id=@id) where yx_nextid=@id
update YX_Article_CMSClass set yx_previd=(select yx_previd from YX_NewsClass where yx_id=@id ) where yx_previd=@id
Update YX_Article_CMSClass set yx_child=yx_child-1 where yx_id=(select yx_parentid from YX_Article_CMSClass where yx_id=@id)
delete from YX_CMSArticle where yx_newsclass=@id
delete from YX_Article_CMSClass where yx_id=@id



~

CREATE Procedure Pro_deleteProclass
(
@id int
)
as 

update YX_ProductClass set yx_nextid=(select yx_nextid from YX_ProductClass where yx_id=@id) where yx_nextid=@id
update YX_ProductClass set yx_previd=(select yx_previd from YX_ProductClass where yx_id=@id ) where yx_previd=@id
Update YX_ProductClass set yx_child=yx_child-1 where yx_id=(select yx_parentid from YX_ProductClass where yx_id=@id)
delete from YX_Product where yx_class=@id
delete from YX_ProductClass where yx_id=@id





~


CREATE Procedure Pro_deleteclass
(
@id int
)
as 

update YX_NewsClass set yx_nextid=(select yx_nextid from YX_NewsClass where yx_id=@id) where yx_nextid=@id
update YX_NewsClass set yx_previd=(select yx_previd from YX_NewsClass where yx_id=@id ) where yx_previd=@id
Update YX_NewsClass set yx_child=yx_child-1 where yx_id=(select yx_parentid from YX_NewsClass where yx_id=@id)
delete from yx_news where yx_newsclass=@id
delete from YX_NewsClass where yx_id=@id





~


create Procedure Proc_ShopLink
(
@uid int
)
as
begin
 select * from YX_Link where YX_Userid=@Uid
end


~


CREATE proc ShowCorrelation
@ProductID int

as
declare @YX_KeysWord varchar(50),@YX_ProductName varchar(50)
select @YX_KeysWord=YX_KeysWord,@YX_ProductName=YX_ProductName from YX_Product where YX_ID = @ProductID
select top 8  YX_ID,YX_ProductName,YX_Spic   from YX_Product where YX_ProductName like '%'+ @YX_ProductName +'%' or YX_KeysWord like '%'+ @YX_KeysWord +'%' order by YX_AddTime desc

~


CREATE Procedure ShowSql
(
@str varchar(500)
)
as

exec(@str)

~


CREATE Proc ShowVote
(
@Uid int
)
as
begin
select * from YX_Vote where yx_voidtype=0 and YX_SellUser=@Uid
end

~


CREATE Procedure UpClass
(
@YX_ID int
)
as
declare @F_ParentID varchar(100)/*父级分类*/
declare @F_Path varchar(100)/*父级分类路径*/
declare @F_Child int/*父级分类的Child*/
declare @F_PrevID int/*父级分类的同深度分类上个ID*/
declare @F_NextID int/*父级分类的同深度分类下个ID*/
declare @F_OrderID int /*父级分类的排序ID*/
declare @TempPath varchar(100) /*临时路径*/
declare @TempNum int /*临时int型*/
declare @F_RootID int
select  @F_ParentID=YX_ParentID,@F_Path=YX_ParentPath,@F_Child=YX_Child,@F_RootID=YX_RootID,@F_PrevID=YX_PrevID,@F_NextID=YX_NextID
,@F_OrderID=YX_OrderID from YX_productclass where YX_ID=@YX_ID
	/**找到父级分类下的所有分类数目**/
        declare @F_NextClassPath varchar(100)
        set @F_NextClassPath=@F_Path+','+Convert(varchar(100),@YX_ID)
	declare @F_ChildNum int
	select @F_ChildNum=(select count(*) from YX_ProductClass where YX_ParentPath like @F_NextClassPath+'%' and YX_RootID=@F_RootID)
	set @F_ChildNum=@F_ChildNum+1

	/******************************/
	/*找到父级分类的同深度分类的信息*/
        declare @Prv_PrevID int
         declare @Prv_RootID int
if @F_PrevID>0
	begin
                /*该分类的相关信息*/
                declare @NextClassPath varchar(100)/*下属分类的路径*/
            
                select @NextClassPath=YX_ParentPath,@Prv_RootID=YX_RootID from YX_ProductClass where YX_ID=@F_PrevID
                set @NextClassPath=@NextClassPath+','+Convert(varchar(100),@F_PrevID)
		declare @Prv_ChildNum int /*上个分类的数目*/
		select @Prv_ChildNum=(select count(*) from YX_ProductClass where YX_ParentPath like @NextClassPath+'%' and YX_RootID=@Prv_RootID)
                /*求出该分类的总数*/
                set @Prv_ChildNum=@Prv_ChildNum+1
              
                (select @Prv_PrevID=YX_PrevID from YX_ProductClass where YX_ID=@F_PrevID)
                
                   /*修改该分类的位置和上下相关ID*/
                        update YX_ProductClass set YX_PrevID=@YX_ID,YX_NextID=@F_NextID,
                         YX_OrderID=YX_OrderID+@F_ChildNum where YX_ID=@F_PrevID
                            /*修改该分类下的排序ID*/
                            update YX_ProductClass set YX_OrderID=YX_OrderID+@F_ChildNum 
                             where YX_ParentPath like @NextClassPath+'%' and YX_RootID=@Prv_RootID 
			/*************************/
                /*****************/
                /**修改该分类上级分类的nextID*/
                update YX_productclass set YX_NextID=@YX_ID where YX_ID=@Prv_PrevID

			/*修改源分类ID*/
			set @TempPath=@F_Path+','+Convert(varchar(100),@YX_ID)
   			update YX_ProductClass set YX_PrevID=@Prv_PrevID,YX_NextID=@F_PrevID,
                        YX_OrderID=YX_OrderID-@Prv_ChildNum where YX_ID=@YX_ID
                            /*修改该分类下的排序ID*/
                        update YX_ProductClass set YX_OrderID=YX_OrderID-@Prv_ChildNum 
                        where YX_ParentPath like @TempPath+'%' and YX_RootID=@F_RootID  
			/*************/
		if @F_NextID >0
		begin
                  /*修改下个相临类别的PrevID*/
                Update YX_ProductClass set YX_PrevID=@F_PrevID where YX_ID=@F_NextID

		end
		
	end


~


CREATE Procedure UpNewsClass
(
@YX_ID int
)
as
declare @F_ParentID varchar(100)/*父级分类*/
declare @F_Path varchar(100)/*父级分类路径*/
declare @F_Child int/*父级分类的Child*/
declare @F_PrevID int/*父级分类的同深度分类上个ID*/
declare @F_NextID int/*父级分类的同深度分类下个ID*/
declare @F_OrderID int /*父级分类的排序ID*/
declare @TempPath varchar(100) /*临时路径*/
declare @TempNum int /*临时int型*/
declare @F_RootID int
select  @F_ParentID=YX_ParentID,@F_Path=YX_ParentPath,@F_Child=YX_Child,@F_RootID=YX_RootID,@F_PrevID=YX_PrevID,@F_NextID=YX_NextID
,@F_OrderID=YX_OrderID from YX_NewsClass where YX_ID=@YX_ID
	/**找到父级分类下的所有分类数目**/
        declare @F_NextClassPath varchar(100)
        set @F_NextClassPath=@F_Path+','+Convert(varchar(100),@YX_ID)
	declare @F_ChildNum int
	select @F_ChildNum=(select count(*) from YX_NewsClass where YX_ParentPath like @F_NextClassPath+'%' and YX_RootID=@F_RootID)
	set @F_ChildNum=@F_ChildNum+1

	/******************************/
	/*找到父级分类的同深度分类的信息*/
        declare @Prv_PrevID int
         declare @Prv_RootID int
if @F_PrevID>0
	begin
                /*该分类的相关信息*/
                declare @NextClassPath varchar(100)/*下属分类的路径*/
            
                select @NextClassPath=YX_ParentPath,@Prv_RootID=YX_RootID from YX_NewsClass where YX_ID=@F_PrevID
                set @NextClassPath=@NextClassPath+','+Convert(varchar(100),@F_PrevID)
		declare @Prv_ChildNum int /*上个分类的数目*/
		select @Prv_ChildNum=(select count(*) from YX_NewsClass where YX_ParentPath like @NextClassPath+'%' and YX_RootID=@Prv_RootID)
                /*求出该分类的总数*/
                set @Prv_ChildNum=@Prv_ChildNum+1
              
                (select @Prv_PrevID=YX_PrevID from YX_NewsClass where YX_ID=@F_PrevID)
                
                   /*修改该分类的位置和上下相关ID*/
                        update YX_NewsClass set YX_PrevID=@YX_ID,YX_NextID=@F_NextID,
                         YX_OrderID=YX_OrderID+@F_ChildNum where YX_ID=@F_PrevID
                            /*修改该分类下的排序ID*/
                            update YX_NewsClass set YX_OrderID=YX_OrderID+@F_ChildNum 
                             where YX_ParentPath like @NextClassPath+'%' and YX_RootID=@Prv_RootID 
			/*************************/
                /*****************/
                /**修改该分类上级分类的nextID*/
                update YX_NewsClass set YX_NextID=@YX_ID where YX_ID=@Prv_PrevID

			/*修改源分类ID*/
			set @TempPath=@F_Path+','+Convert(varchar(100),@YX_ID)
   			update YX_NewsClass set YX_PrevID=@Prv_PrevID,YX_NextID=@F_PrevID,
                        YX_OrderID=YX_OrderID-@Prv_ChildNum where YX_ID=@YX_ID
                            /*修改该分类下的排序ID*/
                        update YX_NewsClass set YX_OrderID=YX_OrderID-@Prv_ChildNum 
                        where YX_ParentPath like @TempPath+'%' and YX_RootID=@F_RootID  
			/*************/
		if @F_NextID >0
		begin
                  /*修改下个相临类别的PrevID*/
                Update YX_NewsClass set YX_PrevID=@F_PrevID where YX_ID=@F_NextID

		end
		
	end



~


CREATE proc UpProductDZ
@Pro_DZ varchar(50)
as
update YX_Product set YX_ProDZ=(YX_ProDZ+','+@Pro_DZ)

~


CREATE proc UpdateShopbase
(
@YX_ShopName varchar(50),
@YX_ShopClass int,
@YX_ShopProject varchar(60),
@YX_ShopContent varchar(3000),
@YX_SHOPUserID int,
@YX_SHOPPinPai  int
)
 as
update YX_Shop set YX_ShopName=@YX_ShopName,YX_ShopClass=@YX_ShopClass,YX_ShopProject=@YX_ShopProject,YX_ShopContent=@YX_ShopContent,YX_SHOPPinPai =@YX_SHOPPinPai  where YX_SHOPUserID=@YX_SHOPUserID

~


/*-----------添加新闻频道----------*/
CREATE procedure YX_CMSAddChannel 
@YX_ChannelName  varchar(50),            /*频道名称*/
@YX_ChannelExc  varchar(100),           /*频道注释*/
@YX_ChannelType  int,                 /*频道类型*/
@YX_ChannelMode int,                 /*频道方案*/
@YX_ChannelDir varchar(50),        /*频道文件夹*/
@YX_ChannelStat int,               /*频道状态*/
@YX_ChannelUrl varchar(50),     /*外部连接地址*/
@YX_Template int
as

insert into YX_Channel (YX_ChannelName,YX_ChannelExc,YX_ChannelType,YX_ChannelMode,YX_ChannelDir,YX_ChannelStat,YX_ChannelUrl,YX_Template)
values (@YX_ChannelName,@YX_ChannelExc,@YX_ChannelType,@YX_ChannelMode,@YX_ChannelDir,@YX_ChannelStat,@YX_ChannelUrl,@YX_Template)


~



CREATE procedure YX_PagerList
(
    @tblName         varchar(255),              -- 表名
    @fldName         varchar(255),              -- 主键字段名
    @PageSize        int = 10,                     -- 页尺寸
    @PageIndex       int = 1,                       -- 页码
    @IsReCount       int = 0,                       -- 返回记录总数, 非0 值则返回
    @OrderType       int = 0,                       -- 设置排序类型, 非0 值则降序
    @strWhere        varchar(5000) = '',      -- 查询条件(注意: 不要加where)
    @orderfld        varchar(2550),
    @colname         varchar(5000)='*'       --查询出来的字段
)
AS
begin
        declare @strSQL   varchar(5000)        -- 主语句
	declare @strTmp   varchar(1000)          -- 临时变量
	declare @strOrder varchar(4000)          -- 排序类型
	declare @strOrder2 varchar(4000)        ---子排序

	if @OrderType != 0
	begin
		if len(@orderfld) = 0
			begin
				set @orderfld = @fldName
			end
		if  @orderfld=@fldName
		begin
			set @strTmp = '<(select min'
			set @strOrder = ' order by [' + @orderfld +'] desc'
		end 
		else
		begin
			set @strTmp = '<=(select min'
			set @strOrder = ' order by [' + @orderfld +'] desc,['+@fldName+'] desc'
		end 
		set @strOrder2 = ' order by [' + @orderfld +'] desc,['+@fldName+'] asc'
	end
	else
	begin
		
		if  @orderfld=@fldName
		begin
		     set @strTmp = '>(select max'
		     set @strOrder = ' order by [' + @orderfld +'] asc'	
		end  
		else
		begin
		     set @strTmp = '>=(select max'
		     set @strOrder = ' order by [' + @orderfld +'] asc,['+@fldName+'] asc'
		end 
		set @strOrder2 = ' order by [' + @orderfld +'] asc,['+@fldName+'] desc'
	end
      
	if @colname=''
	begin
		set @colname='*'
	end 





	if @orderfld!=@fldName 
	begin		

	set @strSQL = 'select top ' + str(@PageSize) + '  '+@colname+'  from ['
		+ @tblName + '] where [' + @orderfld + ']' + @strTmp + '(['
		+ @orderfld + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
		+ @orderfld + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
		+ 'and  '+@fldName+' not in'
		+ '(select top ' + str((@PageIndex-1)*@PageSize) + ' ['+@fldName+'] from ['+@tblName+'] '+@strOrder2+')'
		+ @strOrder2

	if @strWhere != ''
		set @strSQL = 'select top ' + str(@PageSize) + '  '+@colname+'  from ['
			+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
			+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
			+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
			+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
	end 
	else
	begin
		set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
		+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
		+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
		+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
		+ @strOrder

		if @strWhere != ''
		set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
			+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
			+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
			+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
			+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

	end 

	set @strTmp =''
	if @strWhere != ''
		set @strTmp = ' where ' + @strWhere

	if @PageIndex = 1
	begin
		set @strSQL = 'select top ' + str(@PageSize) + '  '+@colname+'  from ['
			+ @tblName + ']' + @strTmp + ' ' + @strOrder
	end

	if @IsReCount != 0
		set @strSQL = 'select count('+ @fldName +') as Total from [' + @tblName + ']'+ @strTmp
		

	 
	exec (@strSQL)
print @strSQL
end

~


CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名 
@strGetFields varchar(1000) = '*', -- 需要返回的列 
@fldName varchar(255)='', -- ID的字段名 
@fldorderName varchar(255)='', -- 排序的字段名 
@PageSize int = 10, -- 页尺寸 
@PageIndex int = 1, -- 页码 
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回 
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where) 
AS 
declare @strSQL varchar(5000) -- 主语句 
declare @strTmp varchar(110) -- 临时变量 
declare @strOrder varchar(400) -- 排序类型id 
declare @strOrdername varchar(400)-- 排序字段
if @doCount != 0 
begin 
if @strWhere !='' 
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere 
else 
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end 
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况 

else 
begin 
if @OrderType != 0 
begin 
set @strTmp = '<(select min' 
set @strOrder = ' order by [' + @fldName +'] desc' 
--如果@OrderType不是0，就执行降序，这句很重要！ 
end 

else 
begin 
set @strTmp = '>(select max' 
set @strOrder = ' order by [' + @fldName +'] asc' 
end 
if @PageIndex = 1 
begin 
if @strWhere != '' 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder 
else 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder 
--如果是第一页就执行以上代码，这样会加快执行速度 
end 

else 
begin 
--以下代码赋予了@strSQL以真正执行的SQL代码 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' 
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder 
if @strWhere != '' 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' 
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '([' 
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' 
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' 
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
end 
end 
print @strSQL
exec (@strSQL)

~


CREATE Procedure updatepaipai
(
@yx_proid int,
@yx_reusercj  float,
@paipaiid int
)
as
begin
declare @strsql varchar(1000)
declare @strsql2 varchar(200)
declare @strsql3 varchar(200)

set @strsql='insert into yx_orderform(yx_orderformid,yx_proid,yx_proname,yx_userid,yx_reuserid,yx_sumcount,yx_sumprice,yx_orderstart,yx_orderdate,yx_rgoodsuser,yx_userpromoble,yx_rgoodstel,yx_rgoodsaddr,yx_rgoodscode,YX_DGoodsType,yx_dgoodsprice) select top 1  yx_orderformid,YX_proid,YX_proname,YX_userid,YX_reuserid,yx_sumcount,YX_reusercj,yx_orderstart,YX_adddate,YX_RGoodsUser,yx_userpromoble,YX_RGoodsTel,YX_RGoodsAddr,YX_RGoodsCode,YX_DGoodsType,YX_DGoodsPrice from yx_paipai where yx_proid='+str(@yx_proid)+' order by yx_reusercj desc'
exec(@strsql)

set @strsql2='update yx_paipai set YX_ok=1,yx_jsdate=getdate(),yx_paimaizt=2 where  yx_proid='+str(@yx_proid)+'  and yx_id='+str(@paipaiid)+''
print @strsql2
exec(@strsql2)

set @strsql3='update yx_paipai set YX_ok=1,yx_jsdate=getdate(),yx_paimaizt=1 where  yx_proid='+str(@yx_proid)+' and yx_id<>'+str(@paipaiid)+''
exec(@strsql3)
end

~

CREATE Procedure Pro_ShowBuyProduct
(
@id int
)
as
select * from yx_buyproduct where yx_id=@id
