SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Urgent help with apostrophe in T-Sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swenri
Yak Posting Veteran

72 Posts

Posted - 09/26/2013 :  15:57:41  Show Profile  Reply with Quote
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

USA
36932 Posts

Posted - 09/26/2013 :  16:05:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need to escape it: 'Crohn''s Disease'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 09/27/2013 :  04:30:07  Show Profile  Reply with Quote
That should be 'Crohn''s Disease'

--
Chandu
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
329 Posts

Posted - 09/27/2013 :  07:17:25  Show Profile  Reply with Quote
What Tara and Chandu presented is two single quote marks for the apostraphy.

djj
Go to Top of Page

swenri
Yak Posting Veteran

72 Posts

Posted - 09/27/2013 :  10:35:10  Show Profile  Reply with Quote
Thank you very much
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000