数据库修改记录_刘超.sql 6.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129
  1. /*订单表 [tb_ErpOrder]
  2. 添加以下两个字段
  3. [Ord_SelectionFilm] --保存选片信息
  4. [Ord_SelectionDesign] --保存选设计信息
  5. [Ord_PhotoBackupPath] --保存相片备份路径信息
  6. 修改时间:2014-12-21*/
  7. --dbo.tb_ErpOrderProductList
  8. /*--判断字段的存在性:
  9. select count(*) from syscolumns
  10. where
  11. id = (select id from sysobjects where type='U' and name='你的表名')
  12. and name = '你要判断的字段名'*/
  13. /****2015-02-08 tb_ErpOrderProductList订单商品表 添加 OPlist_SelectFilmProductLog 和 OPlist_OldProductID 字段****/
  14. IF NOT EXISTS (select * from syscolumns where id= (select id from sysobjects where type='U' and name='tb_ErpOrderProductList') and name = 'OPlist_SelectFilmProductLog')
  15. BEGIN
  16. alter table dbo.tb_ErpOrderProductList add OPlist_SelectFilmProductLog nvarchar(max)
  17. 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'
  18. ALTER TABLE [dbo].[tb_ErpOrderProductList] ADD CONSTRAINT [DF_tb_ErpOrderProductList_OPlist_SelectFilmProductLog] DEFAULT (N'') FOR [OPlist_SelectFilmProductLog]
  19. END
  20. go
  21. IF NOT EXISTS (select * from syscolumns where id= (select id from sysobjects where type='U' and name='tb_ErpOrderProductList') and name = 'OPlist_OldProductID')
  22. BEGIN
  23. alter table dbo.tb_ErpOrderProductList add OPlist_OldProductID nvarchar(100)
  24. 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'
  25. ALTER TABLE [dbo].[tb_ErpOrderProductList] ADD CONSTRAINT [DF_tb_ErpOrderProductList_OPlist_OldProductID] DEFAULT (N'') FOR [OPlist_OldProductID]
  26. END
  27. /***End***/
  28. GO
  29. /****2015-02-10 tb_ErpPlusPickItems订单加挑商品表添加Plu_SourceType 加挑金额来源类型字段 0 为后期二销 1 为选片加挑二销****/
  30. IF NOT EXISTS (select * from syscolumns where id= (select id from sysobjects where type='U' and name='tb_ErpPlusPickItems') and name = 'Plu_SourceType')
  31. BEGIN
  32. alter table tb_ErpPlusPickItems add Plu_SourceType char(2)
  33. 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'
  34. ALTER TABLE [dbo].[tb_ErpPlusPickItems] ADD CONSTRAINT [DF_tb_ErpPlusPickItems_Plu_SourceType] DEFAULT (N'0') FOR [Plu_SourceType]
  35. END
  36. GO
  37. /****2015-02-10 tb_ErpOrder订单表添加Ord_PlusPickNumber 加挑张数字段****/
  38. IF NOT EXISTS (select * from syscolumns where id= (select id from sysobjects where type='U' and name='tb_ErpOrder') and name = 'Ord_PlusPickNumber')
  39. BEGIN
  40. alter table tb_ErpOrder add Ord_PlusPickNumber int
  41. 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'
  42. ALTER TABLE [dbo].[tb_ErpOrder] ADD CONSTRAINT [DF_tb_ErpOrder_Ord_PlusPickNumber] DEFAULT (0) FOR [Ord_PlusPickNumber]
  43. END
  44. /***存储过程***/
  45. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderPROCE_SQL2005PAG]') AND type in (N'P', N'PC'))
  46. BEGIN
  47. EXEC dbo.sp_executesql @statement = N'-- =============================================
  48. -- Author: 刘超
  49. -- Create date: 2014-11-12
  50. -- Description: 获取订单数据分页
  51. -- =============================================
  52. CREATE PROCEDURE [dbo].[OrderPROCE_SQL2005PAG]
  53. (
  54. @TableName varchar(800), --表名
  55. @ReFieldsStr varchar(2000) = ''*'', --字段名(全部字段为*)
  56. @OrderFieldName varchar(2000), --排序字段(可以为空!不支持多字段不用加order by 和 desc或asc)
  57. @WhereString varchar(2000) =N'''', --条件语句(不用加where)
  58. @PageSize int, --每页多少条记录
  59. @PageIndex int = 1 , --指定当前为第几页
  60. @OrderType int=0, --排序方式 0 为正序 1 为倒序
  61. @TotalRecord int output --返回总记录数
  62. )
  63. AS
  64. --目前最快分页过程
  65. BEGIN
  66. --处理开始点和结束点
  67. Declare @MaxRecord int;
  68. Declare @TotalCountSql nvarchar(2000);
  69. Declare @SqlString nvarchar(2000);
  70. Declare @OrderTypeStr nvarchar(200);
  71. Declare @OrderString varchar(2000);
  72. set @OrderTypeStr='' '';
  73. set @OrderString='' '';
  74. IF (@WhereString! = '''' or @WhereString!=null)
  75. BEGIN
  76. SET @WhereString ='' where ''+ @WhereString;
  77. END
  78. SET @TotalCountSql= N''
  79. WITH t as
  80. (
  81. select Ord_ViceNumber from ''+ @TableName+@WhereString+'' Group by [Ord_ViceNumber]
  82. )
  83. select @TotalRecord =count(Ord_ViceNumber) from t'';--总记录数语句
  84. --
  85. EXEC sp_executesql @totalCountSql,N''@TotalRecord int out'',@TotalRecord output;--返回总记录数
  86. ----执行主语句
  87. Declare @TempOrderString varchar(2000);
  88. if(@OrderType=1)
  89. begin
  90. set @OrderTypeStr='' desc '';
  91. end
  92. SET @OrderString ='' [Ord_ViceNumber] ''+@OrderTypeStr;
  93. set @TempOrderString= @OrderString;
  94. IF (@OrderFieldName!='''' or @OrderFieldName!=null)
  95. BEGIN
  96. SET @OrderString ='' ''+@OrderFieldName +@OrderTypeStr;
  97. set @TempOrderString= ''max(''+@OrderFieldName+'') ''+@OrderTypeStr;
  98. END
  99. set @MaxRecord=@PageSize*(@PageIndex-1)
  100. set @SqlString=''Declare @OrdersPerson varchar(4000);
  101. WITH t as
  102. (
  103. select row_number()
  104. over(order by ''+@TempOrderString+'' ) as rowId,Ord_ViceNumber from ''+ @TableName+@WhereString+'' Group by [Ord_ViceNumber]
  105. )
  106. 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
  107. if substring(@OrdersPerson,len(@OrdersPerson),1) = '''',''''
  108. begin
  109. set @OrdersPerson = left(@OrdersPerson,len(@OrdersPerson) - 1)
  110. end
  111. set @OrdersPerson='''' SELECT ''+@ReFieldsStr +'' FROM ''+ @TableName+'' where Ord_ViceNumber in (''''+ @OrdersPerson +'''') order by ''+@OrderString+'' ''''
  112. exec (@OrdersPerson)''
  113. Exec(@SqlString)
  114. END'
  115. END
  116. GO