Tuesday, July 31, 2018

"Like" query example in SQL Query

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

     

No comments:

Post a Comment