Problem passing a name with a ' to the Store Prod.

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

General SQL Server Forums

New to SQL Server Programming

Problem passing a name with a ' to the Store Prod.

Author

Topic

gbeford
Starting Member

9 Posts

Posted - 2009-04-27 : 12:01:36

I have a stored procedure that looks up info from a persons name however I have having issues with someone that has an ' in there name. Can someone show me how to fix this ?

I know that in order to test the select i needed to add an additional ' to the name like in the set.. I just do not know how to do that to the @AgentName ?

declare @AgentName varchar(50)

set @AgentName = 'o''Neal, Carlos'

Select DerivedLocationType,
DerivedLocation,
DerivedDepartment,
Supervisor,
AgentName,
SalesTechID,
CSGOpID,
CSGLogon,
ICOMSLogon,
AvayaLogon,
NTLogon,
Email
From dbo.TD_SAID_Employees
where (AgentName = @AgentName)
and (Available = 0
or (Available = -1 and Termdate > getdate()))

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 12:17:21

It should already work?

gbeford
Starting Member

9 Posts

Posted - 2009-04-27 : 12:53:18

It will in this test because I added the extra ' , however when my webpage passes it, how do I have the store procedure handle it ?

yonabout
Posting Yak Master

112 Posts

Posted - 2009-04-27 : 13:00:45

Wouldn't it be easier to test for this in your webpage?

So you check the name passed in from your page, and replace any " ' " characters with " '' ".

I'm probably wrong, but I don't think the stored procedure is ever going to be able to handle it properly.

Cheers,

Yonabout

maeenul
Starting Member

20 Posts

Posted - 2009-04-28 : 00:08:18

before passing the name from the webpage to the stored procedure, you can just do an extra checking. Replace the single quote with two single quotes and then pass it to the stored procedure. the below stored procedure works. So if you send the name with 2 single quotes, stored procedure does not take it wrong.

CREATE PROCEDURE test1
@Param1 as varchar(50)
AS
BEGIN
SELECT @Param1
END
GO

exec test1 'ami''asdf'

-----------------------
maeenul
http://www.programmingsolution.net/sqlserver2005/sqlserversolutions/index.php
http://sqlservertipsntricks.blogspot.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 02:39:48

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-28 : 12:41:53

If you are using parameterized queries you shouldn't have a problem. You are using parameterized queries aren’t you?

[url]http://www.uberasp.net/getarticle.aspx?id=46[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources