USE [DBName]
GO
/****** Object: StoredProcedure [dbo].[GetECONos] Script Date: 07/31/2018 04:24:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec GetECONos '09907'
ALTER PROCEDURE [dbo].[GetECONos]
@ecoNo nvarchar(50)
AS
BEGIN
IF OBJECT_ID(N'TEMPDB..#tmpProjects') IS NOT NULL
BEGIN
DROP TABLE #tmpProjects
END
SELECT CASE WHEN LEN(CONVERT(nvarchar(50), PROJECT_NO)) >=5 THEN
CONVERT(nvarchar(50),PROJECT_NO)
ELSE
RIGHT('00000' + CONVERT(nvarchar(50), PROJECT_NO), 5) END
AS PROJECT_NO,
CASE WHEN LEN(CONVERT(nvarchar(50), INCREMENT_NO)) >=3 THEN
CONVERT(nvarchar(50),INCREMENT_NO)
ELSE
RIGHT('000' + CONVERT(nvarchar(50), INCREMENT_NO), 3) END
AS INCREMENT_NO,
CONVERT(nvarchar(50),AMENDMENT_NO) AS AMENDMENT_NO,
PROJECT_NAME As Title, PROJECTID as ProjectId
INTO
#tmpProjects
FROM dbo.Project
SELECT (CONVERT(nvarchar(50),PROJECT_NO) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO)) AS ECONo,
Title,ProjectId FROM
#tmpProjects
where (CONVERT(nvarchar(50),PROJECT_NO) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO))
like '%'+@ecoNo+'%'
END
GO
/****** Object: StoredProcedure [dbo].[GetECONos] Script Date: 07/31/2018 04:24:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec GetECONos '09907'
ALTER PROCEDURE [dbo].[GetECONos]
@ecoNo nvarchar(50)
AS
BEGIN
IF OBJECT_ID(N'TEMPDB..#tmpProjects') IS NOT NULL
BEGIN
DROP TABLE #tmpProjects
END
SELECT CASE WHEN LEN(CONVERT(nvarchar(50), PROJECT_NO)) >=5 THEN
CONVERT(nvarchar(50),PROJECT_NO)
ELSE
RIGHT('00000' + CONVERT(nvarchar(50), PROJECT_NO), 5) END
AS PROJECT_NO,
CASE WHEN LEN(CONVERT(nvarchar(50), INCREMENT_NO)) >=3 THEN
CONVERT(nvarchar(50),INCREMENT_NO)
ELSE
RIGHT('000' + CONVERT(nvarchar(50), INCREMENT_NO), 3) END
AS INCREMENT_NO,
CONVERT(nvarchar(50),AMENDMENT_NO) AS AMENDMENT_NO,
PROJECT_NAME As Title, PROJECTID as ProjectId
INTO
#tmpProjects
FROM dbo.Project
SELECT (CONVERT(nvarchar(50),PROJECT_NO) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO)) AS ECONo,
Title,ProjectId FROM
#tmpProjects
where (CONVERT(nvarchar(50),PROJECT_NO) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO))
like '%'+@ecoNo+'%'
END
No comments:
Post a Comment