Urgent help with apostrophe in T-Sql

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)

Urgent help with apostrophe in T-Sql

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2013-09-26 : 15:57:41

I'm having a problem that I need to get the

"BVProblems".Problem IN('Parenchymal Renal Disease','Crohn’s Disease')

The problem is how to select Crohn's disease in an IN statement when a text has apostrophe in it. Please find the below stored proc that I wrote. Looking for urgent help. Thank you ..

USE tempDB

IF OBJECT_ID(N'tempdb..#Result1', N'U')
IS NOT NULL
DROP TABLE #Result1;

GO

USE [DatamartDB2]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_FN_RiskAssessmentrpt]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MMC_SP_FN_RiskAssessmentrpt]
GO

USE [DatamartDB2]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[MMC_SP_FN_RiskAssessmentrpt]

AS

BEGIN

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @Location varchar(255)

BEGIN

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

SET @RptTimeFrameout='1'
if @RptTimeFrameout='1' -- Daily
Begin
select @FromDate =convert(datetime,convert(varchar(10),dateadd(dd,-1, getdate()), 101))
select @ToDate=dateadd(ss, 1, convert(datetime,convert(varchar(10), getdate(),101)))

End

SELECT DISTINCT
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,

isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Problem,

@FromDate AS Fromdate,
@ToDate AS Todate INTO #Result1
FROM ((((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"

AND ("BLOrdersLog"."Text_str" LIKE '%Order NPO%'
OR "BLOrdersLog"."Text_str" LIKE '%diet%'
OR "BLOrdersLog"."Text_str" LIKE '%Order NPO + Ice Chips%')
AND "BLOrdersLog"."Text_str" IS NOT NULL)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"
AND "BVProblems".Existence IN('exists','yes')

AND "BVProblems".Problem IN('Parenchymal Renal Disease','Crohn’s Disease')
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes')
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")

LEFT OUTER JOIN "DatamartDB2"."dbo"."IPR_Height_Weight_Nutritional_Assessment" "HtWt"
ON "BLSession_Extended"."sessionID"="HtWt"."SessionID")

LEFT OUTER JOIN "DatamartDB2"."dbo"."IPR_VTE_Risk_Assessment_VTE_Risk_Assessment" "IPRVTE"
ON "BLSession_Extended"."sessionID" = "IPRVTE"."SessionID")

WHERE

"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000
AND "MO_Demographics"."MRN" = '12345555'
AND "BLOrdersLog"."CancelledSig" IS NULL
AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL

ORDER BY
"bllocation"."location_name" ASC

SELECT rlt1.*
FROM #Result1 rlt1
where dietorderedtime in
(SELECT TOP 1 dietorderedtime
from #Result1 rlt2
where rlt1.mrn=rlt2.mrn
order by dietorderedtime desc)

END

END

SET NOCOUNT OFF

SET ANSI_NULLS OFF

GO

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

IF OBJECT_ID(N'tempdb..#Result1', N'U')
IS NOT NULL
DROP TABLE #Result1;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-26 : 16:05:07

You need to escape it: 'Crohn''s Disease'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/