Problem with LIKe in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with LIKe in stored procedure

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2013-11-21 : 11:04:56

I've a query I need to use like statement where I need to look for "BLUserFreeText"."UserFreeText" LIKE '%SMA%' OR "BLUserFreeText"."UserFreeText" LIKE '%SMN%' . But, the problem is the query is taking a long time to run. If I use UNION the query runs faster but, I'm getting duplicates. Can anybody let me know how to make the query to run faster avoid duplicates using like statement.

This is urgent and thank you.

USE [DatamartDB2]
GO

/****** Object: StoredProcedure [dbo].[MMC_SP_MMC_SP_OBClinicLabsOrders] Script Date: 03/01/2013 18:49:40 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_OBClinicLabsOrders]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MMC_SP_OBClinicLabsOrders]
GO

USE [DatamartDB2]
GO

/****** Object: StoredProcedure [dbo].[MMC_SP_OBClinicLabsOrders] Script Date: 11/12/2013 13:38:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[MMC_SP_OBClinicLabsOrders]

AS

BEGIN

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

PRINT 'FOOD AND NUTRITION DIET CHANGE ORDERS'
DECLARE @Location varchar(255)

if (ISNULL(@Location,'')='')
begin

/* Declare Date Parameters */
--DECLARE @FromDate datetime
--DECLARE @ReAdmitDate datetime
--SET @FromDate = (CONVERT (varchar, Getdate()-120, 101) + ' 00:00:01')
--SET @ReAdmitDate = (CONVERT (varchar, Getdate()-1, 101) + ' 23:59:59')

Declare @FromDate datetime
Declare @ToDate datetime
Declare @RptTimeFrameout VARCHAR(50)

--/* Hardcode for testing */
--SET @FromDate = '2013/11/14'
--SET @ToDate = '2013/11/16'

SET @RptTimeFrameout='1' --daily
if @RptTimeFrameout='1' -- Daily
Begin

select @FromDate = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '00:00:00 AM')
select @ToDate = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE() -1,101))+'23:59:59 PM')
End

SELECT DISTINCT

"BLSession_Extended"."sessionid",
("BLSession_Extended"."LastName" + ' ' + "BLSession_Extended"."firstname") AS Patient_Name,
"BLSession_Extended"."MRN",
"MO_Demographics"."AccountNumber",

(select top 1 "SVOrdersLog"."DrugName"
from "SVOrdersLog"
where "BLSession_Extended"."sessionid" = "SVOrdersLog"."sessionid"
AND "SVOrdersLog"."DrugName" IN ('Order AFP Maternal Serum','Order Maternal Serum AFP','Maternal AFP4 (Quad) screen for risk of T21, T18, NTD')
AND "SVOrdersLog"."DoneSigTime" BETWEEN @FromDate AND @ToDate
AND "SVOrdersLog"."itemstatus" = 'Done'
AND "SVOrdersLog"."DoneSigTime" IS NOT NULL
order by "SVOrdersLog"."DoneSigTime" ASC
) AS OrderAFP,

(select top 1 "SVOrdersLog"."DoneSigTime"
from "SVOrdersLog"
where "BLSession_Extended"."sessionid" = "SVOrdersLog"."sessionid"
AND "SVOrdersLog"."DrugName" IN ('Order AFP Maternal Serum','Order Maternal Serum AFP')
AND "SVOrdersLog"."DoneSigTime" BETWEEN @FromDate AND @ToDate
AND "SVOrdersLog"."itemstatus" = 'Done'
AND "SVOrdersLog"."DoneSigTime" IS NOT NULL
order by "SVOrdersLog"."DoneSigTime" ASC
) AS OrderAFPTime,

(select top 1 "SVOrdersLog"."DrugName"
from "SVOrdersLog"
where "BLSession_Extended"."sessionid" = "SVOrdersLog"."sessionid"
AND "SVOrdersLog"."DrugName"='Order Alfa Fetoprotein Profile Four (AFP4)'
AND "SVOrdersLog"."DoneSigTime" BETWEEN @FromDate AND @ToDate
AND "SVOrdersLog"."itemstatus" = 'Done'
AND "SVOrdersLog"."DoneSigTime" IS NOT NULL
order by "SVOrdersLog"."DoneSigTime" ASC
) AS OrderAFP4,

(select top 1 "SVOrdersLog"."DrugName"
from "SVOrdersLog"
where "BLSession_Extended"."sessionid" = "SVOrdersLog"."sessionid"
AND "SVOrdersLog"."DrugName" = 'Order CF Carrier Screen (CFCS clinic)'
AND "SVOrdersLog"."DoneSigTime" BETWEEN @FromDate AND @ToDate
AND "SVOrdersLog"."itemstatus" = 'Done'
order by "SVOrdersLog"."DoneSigTime" ASC
) AS OrderCFCS,

(select top 1 "SVOrdersLog"."DrugName"
from "SVOrdersLog"
where "BLSession_Extended"."sessionid" = "SVOrdersLog"."sessionid"
AND "SVOrdersLog"."DrugName" IN ('Order Fragile X DNA (FRXDNA clinic)','Order Fragile X DNA (FRXDNA hospital)','Order Fragile X DNA (FRXDNA)','Order Fragile-X Screen')
AND "SVOrdersLog"."DoneSigTime" BETWEEN @FromDate AND @ToDate
AND "SVOrdersLog"."itemstatus" = 'Done'
AND "SVOrdersLog"."DoneSigTime" IS NOT NULL
order by "SVOrdersLog"."DoneSigTime" ASC
) AS OrderFragileDNA,

(select top 1 "SVOrdersLog"."DrugName"
from "SVOrdersLog"
where "BLSession_Extended"."sessionid" = "SVOrdersLog"."sessionid"
AND "SVOrdersLog"."DrugName"='Order Sequential Screen, Part 1'
AND "SVOrdersLog"."DoneSigTime" BETWEEN @FromDate AND @ToDate
AND "SVOrdersLog"."itemstatus" = 'Done'
order by "SVOrdersLog"."DoneSigTime" ASC
) AS OrderSeqScreen1,

(select top 1 "SVOrdersLog"."DrugName"
from "SVOrdersLog"
where "BLSession_Extended"."sessionid" = "SVOrdersLog"."sessionid"
AND "SVOrdersLog"."DrugName"='Order Sequential Screen, Part 2'
AND "SVOrdersLog"."DoneSigTime" BETWEEN @FromDate AND @ToDate
AND "SVOrdersLog"."itemstatus" = 'Done'
AND "SVOrdersLog"."DoneSigTime" IS NOT NULL
order by "SVOrdersLog"."DoneSigTime" ASC
) AS OrderSeqScreen2,

ISNULL((select top 1 "BLUserFreeText"."UserFreeText"
from "BLUserFreeText"
where "BLSession_Extended"."sessionid" = "BLUserFreeText"."sessionid"
AND "BLUserFreeText"."UserFreeText" LIKE '%SMN%'
OR "BLUserFreeText"."UserFreeText" LIKE '%SMA%'
AND "SVOrdersLog"."DoneSigTime" BETWEEN @FromDate AND @ToDate
AND "SVOrdersLog"."itemstatus" = 'Done'
AND "SVOrdersLog"."itemstatus" IS NOT NULL
order by "SVOrdersLog"."DoneSigTime" DESC
),' ')AS OrderSMN,

@FromDate AS FromDt,
@ToDate AS ToDt

FROM (("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."SVOrdersLog" "SVOrdersLog"
ON "BLSession_Extended"."sessionID"="SVOrdersLog"."sessionID"

)
INNER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID"
INNER JOIN "BLUserFreeText"
ON "BLSession_Extended"."sessionid" = "BLUserFreeText"."sessionid"
AND ("BLUserFreeText"."UserFreeText" LIKE '%SMN%')
OR ("BLUserFreeText"."UserFreeText" LIKE '%SMA%')
)
WHERE "BLSession_Extended"."FacilityID"=1
AND "SVOrdersLog"."DoneSigTime" BETWEEN @FromDate AND @ToDate
AND "SVOrdersLog"."DrugName" IN ('Order Alfa Fetoprotein Profile Four (AFP4)',
'Order AFP Maternal Serum','Order Maternal Serum AFP',
'Order CF Carrier Screen (CFCS clinic)',
'Order Fragile X DNA (FRXDNA)',
'Order Sequential Screen, Part 1',
'Order Sequential Screen, Part 2'
)
AND "SVOrdersLog"."itemstatus" = 'Done'
AND "SVOrdersLog"."DoneSigTime" IS NOT NULL
AND "BLSession_Extended"."LastName" NOT LIKE ('%Test%')
AND "BLSession_Extended"."MRN" NOT LIKE ('%@@Versi%')

END

END

SET NOCOUNT OFF

SET ANSI_NULLS OFF

GO

GO
GRANT EXECUTE ON [dbo].[MMC_SP_OBClinicLabsOrders] TO [Public]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-21 : 11:39:21

You probably need a set of paranthesis around the two like clauses - see in red below

....
INNER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics" 
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID"
INNER JOIN "BLUserFreeText" 
ON "BLSession_Extended"."sessionid" = "BLUserFreeText"."sessionid" 
AND (("BLUserFreeText"."UserFreeText" LIKE '%SMN%') 
OR ("BLUserFreeText"."UserFreeText" LIKE '%SMA%'))
) 
WHERE "BLSession_Extended"."FacilityID"=1
...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-21 : 11:59:50

Simplified:

("BLUserFreeText"."UserFreeText" LIKE '%SM[NA]%')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources