April 15, 2011 at 12:46 pm
When a variable in my stored procedure contains an c, 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
April 15, 2011 at 2:57 pm
Can you look at and post the VB code?
If they were using parameters in ADO to call the proc there wouldn't be an issue.
Why are you using @@ for parameters? One @ is all you need.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2011 at 3:04 pm
Paramaters are being passed to call the stored proc
May 4, 2011 at 8:13 pm
Thanks you for the post.
Hi guys, Im a newbie. Nice to join this forum.
__________________
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply