資料欄位型別匹配

/*7*/
--==========================================================================================
-- Generated By:       Changyong Jiang
-- Create Date:        2013/03/25
-- Description: 返回取得的DataInconsistency Alert的資料
-- Generated By:       Changyong Jiang
-- Create Date:        2013/05/24
-- Description:修改時間由原來的獲取上本週的資料，改為獲取昨天的資料

-- Generated By:       Changyong Jiang
-- Create Date:        2013/06/05
-- Description: data dump dumpload order by
--==========================================================================================

CREATE PROCEDURE [dbo].[Alt_Read_ROHSDataDUMP_SP]
@TransType   VARCHAR(100) = '',
@UserName VARCHAR(100) = '',
@ProgramName VARCHAR(MAX) = '',
@Owner VARCHAR(MAX) = '',
@strdate VARCHAR(10) = null,
@DataType VARCHAR(20)='',
@PageIndex INT=1,
@PageSize INT=20


AS
SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @_RowCount   INT
DECLARE @_PageCount INT
DECLARE @_StartRow   INT

CREATE   TABLE #Temp(
Iden BIGINT IDENTITY(1, 1),
[DataType] VARCHAR(30),
[FileName] VARCHAR(200),
[FilePath] VARCHAR(2000),
[CreatedDate] varchar(10)
)

CREATE   TABLE #newTemp(
[DataType] VARCHAR(30),
[FileName] VARCHAR(200),
[FilePath] VARCHAR(2000),
[CreatedDate] varchar(10)
)

    set @strdate=convert(varchar(10),@strdate,120)
    --SELECT convert(varchar(10),GETDATE(),120)
    --SET @strdate='2013-05-08'

   IF @strdate IS NULL
BEGIN
     IF @TransType = 'ROHSDATADUMP_ALERT'
        OR @TransType = 'ROHSDATADUMP_DOWNLOAD'
     BEGIN
         SET @strdate = convert(varchar(10),DATEADD(DAY,-1, GETDATE()),120)
     END
END

   IF @TransType='ROHSDATADUMP_ALERT'
   BEGIN

--解決前臺傳入多個owner
DECLARE @TempOwner TABLE (_Owner VARCHAR(30))
DECLARE @TempProgramName TABLE (_ProgramName VARCHAR(30))
INSERT INTO @TempOwner
      (
     _Owner
      )
    SELECT adapt_object FROM [dbo].[StringToArray](@Owner,',')


    INSERT INTO @TempProgramName
      (
     _ProgramName
      )
    SELECT adapt_object FROM [dbo].[StringToArray](@ProgramName,',')


SELECT
rh.[MPasite] AS [MPa site],
rh.[Program] AS [Program],
rh.[SKU] AS [SKU],
rh.[BOM_Level] AS [BOM Level],
rh.[HPPN] AS [HP PN],
rh.[Description] AS [Description],
rh.[Material_Group] AS [Material Group],
rh.[Control_Code] AS [Control Code],
rh.[Mat_Type] AS [Mat_Type],
rh.[Make2Buy] AS [Make/Buy],
rh.[MFGR] AS [MFGR],
rh.[MPN] AS [MPN],
rh.[Lowest_RoHS_ID] AS [Lowest_RoHS_ID],
rh.[Highest_RoHS_ID] AS [Highest_RoHS_ID],
rh.[Last_Upload_Date] AS [Last Upload Date],
rh.[Change_By] AS [Change By] ,
rh.[SDoC] AS [SDoC] ,
rh.[G_template] AS [G-template]
FROM   RoHSDataDump_History_BomTree(NOLOCK) rh
    --WHERE   CONVERT(varchar(10),rh.[strDate])=@strdate
    WHERE   rh.[strDate]=@strdate
AND rh.[MPasite] in (SELECT _Owner FROM @TempOwner)
and rh.[Program] in (SELECT _ProgramName FROM @TempProgramName)
ORDER BY rh.[MPasite],rh.[Program],rh.[SKU],rh.[intShowNo]

   END

   IF @TransType='ROHSDATADUMP_DOWNLOAD'
   begin

   DECLARE   @_ControlValue nvarchar(100) SET @_ControlValue='2'
   Declare   @_QueryValue varchar(100) set @_QueryValue='8'
   DECLARE   @maxcount int set @maxcount=0
   DECLARE   @maxdate   nvarchar(10)

   select @_ControlValue=ControlValue from ADControlValueM WHERE
   ControlName='ROHSDATA_FILE' and CustomField1='DOWNLOADVALIDWEEKS'


   select @_QueryValue=ControlValue from ADControlValueM WHERE
   ControlName='ROHSDATA_FILE' and CustomField1='ProgramQueryReportTimes'

   declare @_topnum int
   set @_topnum=CONVERT(int ,@_QueryValue)

    truncate table #Temp
    INSERT INTO #Temp
    select
    a.[DataType] as [DataType],
    a.[FileName] as [FileName],
    a.[FilePath] as [FilePath],
     Convert(varchar(10),a.[CreatedDate],103) as [CreatedDate]
     from
    (
select
ddlu.[DataType] as [DataType],
ddlu.[FileName] as [FileName],
ddlu.[FilePath] as [FilePath],
ddlu.[CreatedDate] as [CreatedDate]
from DataDownLoadURL ddlu
where ddlu.[username] =@UserName
and ddlu.[CreatedDate] between DATEADD(week, -CONVERT (INT ,@_ControlValue),
          CONVERT(DATETIME,getdate())) and   CONVERT(DATETIME,getdate())
    and ddlu.[DataType]='ROHSDATADUMP'
    union
select Top (@_topnum)
ddlu.[DataType] as [DataType],
ddlu.[FileName] as [FileName],
ddlu.[FilePath] as [FilePath],
ddlu.[CreatedDate] as [CreatedDate]
from DataDownLoadURL ddlu
where ddlu.[username] =@UserName
and ddlu.[DataType]='ROHSPROGRAMQUERYREPORT'
order by ddlu.[CreatedDate] desc
    ) a
    order by
    --a.[DataType] asc,
    a.[CreatedDate] DESC

SELECT @maxcount=COUNT(1) FROM #Temp


IF (@maxcount<=0)
BEGIN
   select @maxdate=Convert(nvarchar(10),max([CreatedDate]),121) from DataDownLoadURL --獲取表中最大的資料，顯示其中最最後的2周資料
truncate table   #newTemp
   INSERT INTO #newTemp
   select
ddlu.[DataType] as [DataType],
ddlu.[FileName] as [FileName],
ddlu.[FilePath] as [FilePath],
Convert(varchar(10),ddlu.[CreatedDate],103)as [CreatedDate]
from DataDownLoadURL ddlu
where ddlu.[username] =@UserName Or (@UserName is null )
and ddlu.[CreatedDate] between DATEADD(week, -2,CONVERT(DATETIME,@maxdate)) and CONVERT(DATETIME,@maxdate)
and ddlu.[DataType]=@DataType
order by
--ddlu.[DataType] asc,
ddlu.[CreatedDate] DESC

INSERT INTO #Temp SELECT *FROM #newTemp
END

SELECT @_RowCount = COUNT(1) FROM #Temp

   SET @PageIndex = @PageIndex - 1
   IF @PageSize = 0
    SET @PageSize = @_RowCount
   IF @_RowCount > 0
    BEGIN
IF @_RowCount % @PageSize > 0
   SET @_PageCount = @_RowCount / @PageSize + 1
ELSE
   SET @_PageCount = @_RowCount / @PageSize
SET @_StartRow = @PageSize * @PageIndex + 1
    END
   ELSE
    BEGIN
SET @_PageCount = 0
SET @_StartRow = 0
    END
   SELECT TOP(@PageSize) * FROM #Temp WHERE Iden >= @_StartRow
   SELECT @_PageCount AS TotalPages

   DROP TABLE #Temp
   DROP TABLE #newTemp
   end

   IF @TransType='ROHSDATADUMP_PARA'
   BEGIN
      SELECT a.ControlValue,a.CustomField1
      from ADControlValueM a (Nolock)
      WHERE   ControlName='ROHSDATA_FILE' order by a.SeqNo
   END

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-763792/，如需轉載，請註明出處，否則將追究法律責任。

資料欄位型別匹配

相關文章