123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129 |
- /*订单表 [tb_ErpOrder]
- 添加以下两个字段
- [Ord_SelectionFilm] --保存选片信息
- [Ord_SelectionDesign] --保存选设计信息
- [Ord_PhotoBackupPath] --保存相片备份路径信息
- 修改时间:2014-12-21*/
- --dbo.tb_ErpOrderProductList
- /*--判断字段的存在性:
- select count(*) from syscolumns
- where
- id = (select id from sysobjects where type='U' and name='你的表名')
-
- and name = '你要判断的字段名'*/
- /****2015-02-08 tb_ErpOrderProductList订单商品表 添加 OPlist_SelectFilmProductLog 和 OPlist_OldProductID 字段****/
- IF NOT EXISTS (select * from syscolumns where id= (select id from sysobjects where type='U' and name='tb_ErpOrderProductList') and name = 'OPlist_SelectFilmProductLog')
- BEGIN
- alter table dbo.tb_ErpOrderProductList add OPlist_SelectFilmProductLog nvarchar(max)
- EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'选片商品变更日志' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderProductList', @level2type=N'COLUMN',@level2name=N'OPlist_SelectFilmProductLog'
- ALTER TABLE [dbo].[tb_ErpOrderProductList] ADD CONSTRAINT [DF_tb_ErpOrderProductList_OPlist_SelectFilmProductLog] DEFAULT (N'') FOR [OPlist_SelectFilmProductLog]
- END
- go
- IF NOT EXISTS (select * from syscolumns where id= (select id from sysobjects where type='U' and name='tb_ErpOrderProductList') and name = 'OPlist_OldProductID')
- BEGIN
- alter table dbo.tb_ErpOrderProductList add OPlist_OldProductID nvarchar(100)
- EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'选片时商品变更升级前的旧商品ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderProductList', @level2type=N'COLUMN',@level2name=N'OPlist_OldProductID'
- ALTER TABLE [dbo].[tb_ErpOrderProductList] ADD CONSTRAINT [DF_tb_ErpOrderProductList_OPlist_OldProductID] DEFAULT (N'') FOR [OPlist_OldProductID]
- END
- /***End***/
- GO
- /****2015-02-10 tb_ErpPlusPickItems订单加挑商品表添加Plu_SourceType 加挑金额来源类型字段 0 为后期二销 1 为选片加挑二销****/
- IF NOT EXISTS (select * from syscolumns where id= (select id from sysobjects where type='U' and name='tb_ErpPlusPickItems') and name = 'Plu_SourceType')
- BEGIN
- alter table tb_ErpPlusPickItems add Plu_SourceType char(2)
- EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'加挑金额来源类型 0 为后期二销 1 为选片加挑二销' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpPlusPickItems', @level2type=N'COLUMN',@level2name=N'Plu_SourceType'
- ALTER TABLE [dbo].[tb_ErpPlusPickItems] ADD CONSTRAINT [DF_tb_ErpPlusPickItems_Plu_SourceType] DEFAULT (N'0') FOR [Plu_SourceType]
- END
- GO
- /****2015-02-10 tb_ErpOrder订单表添加Ord_PlusPickNumber 加挑张数字段****/
- IF NOT EXISTS (select * from syscolumns where id= (select id from sysobjects where type='U' and name='tb_ErpOrder') and name = 'Ord_PlusPickNumber')
- BEGIN
- alter table tb_ErpOrder add Ord_PlusPickNumber int
- EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'加挑张数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrder', @level2type=N'COLUMN',@level2name=N'Ord_PlusPickNumber'
- ALTER TABLE [dbo].[tb_ErpOrder] ADD CONSTRAINT [DF_tb_ErpOrder_Ord_PlusPickNumber] DEFAULT (0) FOR [Ord_PlusPickNumber]
- END
- /***存储过程***/
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderPROCE_SQL2005PAG]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'-- =============================================
- -- Author: 刘超
- -- Create date: 2014-11-12
- -- Description: 获取订单数据分页
- -- =============================================
- CREATE PROCEDURE [dbo].[OrderPROCE_SQL2005PAG]
- (
- @TableName varchar(800), --表名
- @ReFieldsStr varchar(2000) = ''*'', --字段名(全部字段为*)
- @OrderFieldName varchar(2000), --排序字段(可以为空!不支持多字段不用加order by 和 desc或asc)
- @WhereString varchar(2000) =N'''', --条件语句(不用加where)
- @PageSize int, --每页多少条记录
- @PageIndex int = 1 , --指定当前为第几页
- @OrderType int=0, --排序方式 0 为正序 1 为倒序
- @TotalRecord int output --返回总记录数
- )
- AS
- --目前最快分页过程
- BEGIN
- --处理开始点和结束点
- Declare @MaxRecord int;
- Declare @TotalCountSql nvarchar(2000);
- Declare @SqlString nvarchar(2000);
- Declare @OrderTypeStr nvarchar(200);
- Declare @OrderString varchar(2000);
- set @OrderTypeStr='' '';
- set @OrderString='' '';
- IF (@WhereString! = '''' or @WhereString!=null)
- BEGIN
- SET @WhereString ='' where ''+ @WhereString;
- END
- SET @TotalCountSql= N''
- WITH t as
- (
- select Ord_ViceNumber from ''+ @TableName+@WhereString+'' Group by [Ord_ViceNumber]
- )
- select @TotalRecord =count(Ord_ViceNumber) from t'';--总记录数语句
- --
- EXEC sp_executesql @totalCountSql,N''@TotalRecord int out'',@TotalRecord output;--返回总记录数
- ----执行主语句
- Declare @TempOrderString varchar(2000);
- if(@OrderType=1)
- begin
- set @OrderTypeStr='' desc '';
- end
- SET @OrderString ='' [Ord_ViceNumber] ''+@OrderTypeStr;
- set @TempOrderString= @OrderString;
- IF (@OrderFieldName!='''' or @OrderFieldName!=null)
- BEGIN
- SET @OrderString ='' ''+@OrderFieldName +@OrderTypeStr;
- set @TempOrderString= ''max(''+@OrderFieldName+'') ''+@OrderTypeStr;
- END
-
-
- set @MaxRecord=@PageSize*(@PageIndex-1)
- set @SqlString=''Declare @OrdersPerson varchar(4000);
- WITH t as
- (
- select row_number()
- over(order by ''+@TempOrderString+'' ) as rowId,Ord_ViceNumber from ''+ @TableName+@WhereString+'' Group by [Ord_ViceNumber]
- )
- select top ''+ltrim(str(@PageSize))+'' @OrdersPerson= isnull(@OrdersPerson,'''''''')+''''''''''''''''+ cast(Ord_ViceNumber as nvarchar(20))+'''''''''''','''' from (select [Ord_ViceNumber] from t where rowId>''+ltrim(str(@MaxRecord))+'') as t2
- if substring(@OrdersPerson,len(@OrdersPerson),1) = '''',''''
- begin
- set @OrdersPerson = left(@OrdersPerson,len(@OrdersPerson) - 1)
- end
- set @OrdersPerson='''' SELECT ''+@ReFieldsStr +'' FROM ''+ @TableName+'' where Ord_ViceNumber in (''''+ @OrdersPerson +'''') order by ''+@OrderString+'' ''''
- exec (@OrdersPerson)''
- Exec(@SqlString)
- END'
- END
- GO
|