Dealing with apostrophe in stored procedure

berba

When a variable in my stored procedure contains an apostrophe, it returns me an error. I've tried the function REPLACE() but no luck. waiting for your suggesions. Thanks

Lost User

berba wrote:

I've tried the function REPLACE() but no luck.

waiting for your suggesions.

I suggest you post the error-message that you got when you tried that function, or the code that you tried.

I are Troll :suss:

Chris Meech

Without seeing the code that uses the variable and how the variable is applied to the code, it's difficult to know what is happening. And also knowing what the error return is will help out as well. Just a guess, but usually embedded apostrophes within strings will need to be escaped somehow. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

thatraja

You must post your query in your question. Replace single quote in sql server[^] BTW enjoy those brief explanations there

thatraja

**My Tip/Tricks
My Dad had a Heart Attack on this day so don't...
**

berba

CREATE PROCEDURE sp_GetEZQueryList @@sUser as nchar(500), @@sSupervisor as char(1), @@sAllowed as varchar(8000) AS --set @@sUser = REPLACE(@@sUser, '''''','''''') IF @@sSupervisor <> 'Y' SELECT EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip FROM EZQuery WHERE (EzqUserName=@@sUser) OR (EzqUserName='SYSTEM' AND Cast(EzqFlxID as varchar(10)) IN (select * FROM intlist_to_tbl(@@sAllowed))) ELSE SELECT EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip FROM EZQuery GO The SP is retrieving @@sUser from VB and its getting the value Do'Sullivan. When I ran a trace I found it is executing the SP like this exec sp_getezquerylist 'DO' + CHAR(39) + 'SULLIVAN', 'n', '100' Think + CHAR(39) + is causing the error Thanks

berba

CREATE PROCEDURE sp_GetEZQueryList @@sUser as nchar(500), @@sSupervisor as char(1), @@sAllowed as varchar(8000) AS --set @@sUser = REPLACE(@@sUser, '''''','''''') IF @@sSupervisor <> 'Y' SELECT EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip FROM EZQuery WHERE (EzqUserName=@@sUser) OR (EzqUserName='SYSTEM' AND Cast(EzqFlxID as varchar(10)) IN (select * FROM intlist_to_tbl(@@sAllowed))) ELSE SELECT EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip FROM EZQuery GO The SP is retrieving @@sUser from VB and its getting the value Do'Sullivan. When I ran a trace I found it is executing the SP like this exec sp_getezquerylist 'DO' + CHAR(39) + 'SULLIVAN', 'n', '100' Think + CHAR(39) + is causing the error Thanks

berba

Any solutions ?? Cheers

S Douglas

Little Debugging..

CREATE PROCEDURE sp_GetEZQueryList
(
@sUser AS nchar(500) ,
@sSupervisor AS char(1) ,
@sAllowed AS varchar(8000)
)
AS

IF @sSupervisor <> 'Y'
SELECT
EzqDateMod ,
EzqFlxID ,
EzqUserName ,
EzqName ,
EzqData ,
EzqDescrip
FROM
EZQuery
WHERE
( EzqUserName = @sUser )
OR
( EzqUserName = 'SYSTEM' /*AND CAST(EzqFlxID AS varchar(10)) IN ( SELECT * FROM intlist_to_tbl WHERE(@sAllowed) ) */)

ELSE
SELECT
EzqDateMod ,
EzqFlxID ,
EzqUserName ,
EzqName ,
EzqData ,
EzqDescrip
FROM
EZQuery
GO

exec sp_getezquerylist 'DO''SULLIVAN', 'N', '100'

Executes fine, without the IN clase to look up The source of your issues appear to be in this function (I assume its a function based on the nomenclature) intlist_to_tbl. If intlist_to_tbl is really a function why are you sending that data to the db, and converting it instead of storing it in the db?

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.