Apostrophe in Stored Procedure
-
When a variable in my stored procedure contains an apostrophe, it returns me an error. 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 Waiting for your suggestions
-
When a variable in my stored procedure contains an apostrophe, it returns me an error. 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 Waiting for your suggestions
You have already posted this message in this forum. Repeating it will only annoy the people who might otherwise give you a solution.
Software rusts. Simon Stephenson, ca 1994.