---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/****************************************************************
Name: usp_MaterialsSDSMaster_msds_SELECT_All
Author: JinHui
Description: SELECT All Record
Parameters:
Parameters: IN
@CurrentPage 目前頁數
@Pagesize 每頁記錄條數
@TotalRecords 返回的記錄總數
OUT - 1 存在
0 不存在
Usage: exec usp_MaterialsSDSMaster_msds_SELECT_All '','','','','','','','','','','','1',10,'',''
Calling functions:
System: None Module: None
History:
Date Name Description
****************************************************************/
CREATE PROCEDURE [dbo].[usp_MaterialsSDSMaster_msds_SELECT_All]
@msds_cItem varchar(25),
@msds_cSuplierNo char(10),
@msds_cType varchar(1),
@msds_cCmdy varchar(3),
@msds_cBrand varchar(3),
@msds_cManu varchar(10),
@msds_cColor varchar(5),
@msds_cGrain varchar(2),
@msds_cCat varchar(3),
@msds_cSub1 varchar(3),
@msds_cState char(1)
,@iCurrentPage as int,
@iPagesize as int
--anson
,@cDescription as nvarchar(500)
,@cPo as char(13)
--anson --end
AS
SET NOCOUNT ON
--declare
--@msds_cItem varchar(25),
--@msds_cSuplierNo char(10),
--@msds_cType varchar(1),
--@msds_cCmdy varchar(3),
--@msds_cBrand varchar(3),
--@msds_cManu varchar(10),
--@msds_cColor varchar(5),
--@msds_cGrain varchar(2),
--@msds_cCat varchar(3),
--@msds_cSub1 varchar(3),
--@msds_cState char(1)
--
-- ,@iCurrentPage as int,
-- @iPagesize as int,
-- @iTotalRecords as int
--
--
--set @msds_cItem = ''
--set @msds_cSuplierNo = ''
--set @msds_cType = ''
--set @msds_cCmdy = ''
--set @msds_cBrand = ''
--set @msds_cManu = ''
--set @msds_cColor = ''
--set @msds_cGrain = ''
--set @msds_cCat = ''
--set @msds_cSub1 = ''
--set @msds_cState= ''
--
--set @iCurrentPage =1
--set @iPagesize= 10
IF NOT OBJECT_ID('TEMPDB..#MyTemp') IS NULL DROP TABLE #MyTemp
IF NOT OBJECT_ID('TEMPDB..#Result_table') IS NULL DROP TABLE #Result_table
CREATE TABLE #MyTemp
(
msds_iItemID int,
msds_cItem varchar(25),
msds_cDescription nvarchar(500),
msds_cSuplierNo char(10),
msds_cSuplier nvarchar(60),
msds_cType varchar(1),
msds_cCmdy varchar(3),
msds_cBrand varchar(3),
msds_cManu varchar(10),
msds_cColor varchar(5),
msds_cGrain varchar(2),
msds_cCat varchar(3),
msds_cSub1 varchar(3),
msds_cCSP1 nvarchar(30),
msds_cCSP2 nvarchar(30),
msds_cCSP3 nvarchar(30),
msds_cCSP4 nvarchar(30),
msds_cCSP5 nvarchar(30),
msds_cCSP6 nvarchar(30),
msds_cCSP7 nvarchar(30),
msds_cCSP8 nvarchar(30),
msds_cCSP9 nvarchar(30),
msds_cCSP10 nvarchar(30),
msds_cCSPDesc1 nvarchar(30),
msds_cCSPDesc2 nvarchar(30),
msds_cCSPDesc3 nvarchar(30),
msds_cCSPDesc4 nvarchar(30),
msds_cCSPDesc5 nvarchar(30),
msds_cCSPDesc6 nvarchar(30),
msds_cCSPDesc7 nvarchar(30),
msds_cCSPDesc8 nvarchar(30),
msds_cCSPDesc9 nvarchar(30),
msds_cCSPDesc10 nvarchar(30),
msds_iENInfoID int ,
msds_iCHInfoID int ,
msds_cState char(1),
msds_cPo char(13)
,msds_fModificationCount decimal(6,0)
)
DECLARE @SQL NVARCHAR(4000)
DECLARE @iPageCount AS INT
DECLARE @iTotalRecords AS INT
SET @SQL = N'INSERT INTO #MyTemp(msds_iItemID,msds_cItem,msds_cDescription,msds_cSuplierNo,msds_cSuplier,msds_cType,msds_cCmdy,msds_cBrand,
msds_cManu,msds_cColor,msds_cGrain,msds_cCat,msds_cSub1,msds_cCSP1,msds_cCSP2,msds_cCSP3,msds_cCSP4,
msds_cCSP5,msds_cCSP6,msds_cCSP7,msds_cCSP8,msds_cCSP9,msds_cCSP10,msds_cCSPDesc1,msds_cCSPDesc2,
msds_cCSPDesc3,msds_cCSPDesc4,msds_cCSPDesc5,msds_cCSPDesc6,msds_cCSPDesc7,msds_cCSPDesc8,
msds_cCSPDesc9,msds_cCSPDesc10, msds_iENInfoID ,msds_iCHInfoID
,msds_cState, msds_cPo,msds_fModificationCount) ' + CHAR(13)
SET @SQL= @SQL + 'SELECT
msds_iItemID,msds_cItem,msds_cDescription,msds_cSuplierNo,msds_cSuplier,msds_cType,msds_cCmdy,msds_cBrand,
msds_cManu,msds_cColor,msds_cGrain,msds_cCat,msds_cSub1,msds_cCSP1,msds_cCSP2,msds_cCSP3,msds_cCSP4,
msds_cCSP5,msds_cCSP6,msds_cCSP7,msds_cCSP8,msds_cCSP9,msds_cCSP10,msds_cCSPDesc1,msds_cCSPDesc2,
msds_cCSPDesc3,msds_cCSPDesc4,msds_cCSPDesc5,msds_cCSPDesc6,msds_cCSPDesc7,msds_cCSPDesc8,
msds_cCSPDesc9,msds_cCSPDesc10, msds_iENInfoID ,msds_iCHInfoID
,msds_cState, msds_cPo,msds_fModificationCount
FROM MaterialsSDSMaster_msds
WHERE 1 = 1'
IF len(@msds_cItem) <> 0 SET @SQL = @SQL + ' AND msds_cItem =''' + @msds_cItem+'''' + CHAR(13)
IF len(@msds_cSuplierNo) <> 0 SET @SQL = @SQL + ' AND msds_cSuplierNo =''' + @msds_cSuplierNo+'''' + CHAR(13)
IF len(@msds_cType) <> 0 SET @SQL = @SQL + ' AND msds_cType =''' + @msds_cType+'''' + CHAR(13)
IF len(@msds_cCmdy) <> 0 SET @SQL = @SQL + ' AND msds_cCmdy =''' + @msds_cCmdy+'''' + CHAR(13)
IF len(@msds_cBrand) <> 0 SET @SQL = @SQL + ' AND msds_cBrand =''' + @msds_cBrand+'''' + CHAR(13)
IF len(@msds_cManu) <> 0 SET @SQL = @SQL + ' AND msds_cManu =''' + @msds_cManu+'''' + CHAR(13)
IF len(@msds_cColor) <> 0 SET @SQL = @SQL + ' AND msds_cColor =''' + @msds_cColor+'''' + CHAR(13)
IF len(@msds_cGrain) <> 0 SET @SQL = @SQL + ' AND msds_cGrain =''' + @msds_cGrain+'''' + CHAR(13)
IF len(@msds_cCat) <> 0 SET @SQL = @SQL + ' AND msds_cCat =''' + @msds_cCat+'''' + CHAR(13)
IF len(@msds_cSub1) <> 0 SET @SQL = @SQL + ' AND msds_cSub1 =''' + @msds_cSub1+'''' + CHAR(13)
--anson
IF len(@cDescription) <> 0 SET @SQL = @SQL + ' AND msds_cDescription like N''' + @cDescription+'''' + CHAR(13)
IF len(@cPo) <> 0 SET @SQL = @SQL + ' AND msds_cPo like ''' + rtrim(@cPo)+'''' + CHAR(13)
--anson --end
BEGIN
IF @msds_cState='Y'
SET @SQL = @SQL + ' AND msds_cState = '''+@msds_cState+'''' + CHAR(13)
ELSE IF @msds_cState='N'
SET @SQL = @SQL + ' AND msds_cState = '''+@msds_cState+'''' + CHAR(13)
ELSE IF @msds_cState='K'--無資料
SET @SQL = @SQL + ' AND msds_cState = '''+'''' + CHAR(13)
ELSE IF @msds_cState='A' --無資料 + 未完成
SET @SQL = @SQL + ' AND msds_cState <> ''Y''' + CHAR(13)
END
--EXEC (@SQL)
--select * from #MyTemp
--print @SQL
EXECUTE (@SQL)
--最後再Select 一次是為了將自動編號數值欄位添加進來，以便做分頁功能
SELECT IDENTITY(int, 1, 1) as iLine,
msds_iItemID,msds_cItem,msds_cDescription,msds_cSuplierNo,msds_cSuplier,msds_cType,msds_cCmdy,msds_cBrand,
msds_cManu,msds_cColor,msds_cGrain,msds_cCat,msds_cSub1,msds_cCSP1,msds_cCSP2,msds_cCSP3,msds_cCSP4,
msds_cCSP5,msds_cCSP6,msds_cCSP7,msds_cCSP8,msds_cCSP9,msds_cCSP10,msds_cCSPDesc1,msds_cCSPDesc2,
msds_cCSPDesc3,msds_cCSPDesc4,msds_cCSPDesc5,msds_cCSPDesc6,msds_cCSPDesc7,msds_cCSPDesc8,
msds_cCSPDesc9,msds_cCSPDesc10, msds_iENInfoID ,msds_iCHInfoID
,msds_cState, msds_cPo,msds_fModificationCount
INTO #Result_table
FROM #MyTemp
SELECT @iTotalRecords = @@Rowcount
IF @iPagesize >= @iTotalRecords
BEGIN
SELECT *, @iTotalRecords AS iTotalRecords FROM #Result_table
END
ELSE
BEGIN
SET @iPageCount = CONVERT(DECIMAL(10,0), (@iTotalRecords * 0.1) / (@iPagesize * 0.1) )
SELECT *, @iTotalRecords AS iTotalRecords FROM #Result_table
WHERE iLine > (@iCurrentPage - 1) * @iPagesize AND iLine <= ((@iCurrentPage - 1) * @iPagesize + @iPagesize )
END
IF NOT OBJECT_ID('TEMPDB..#MyTemp') IS NULL DROP TABLE #MyTemp
IF NOT OBJECT_ID('TEMPDB..#Result_table') IS NULL DROP TABLE #Result_table
PRINT @SQL
SET NOCOUNT OFF
--GO
--GRANT EXECUTE ON [dbo].[usp_MaterialsSDSMaster_msds_SELECT_All] TO [public]