sql_language thread: Syntax Error:Apostrophe in strored procedure.
Nick,
Thanks for your help.
Finally got there.
With your help the stored procedure is now right. However, I did have to
amend your code a little.
Then in the asp code I had to insert double quotes without it thinking it
was trying to end a string.
There were 2 ways, instead of one double quote, use two quotes to
surround a string.
Or insert the Chr(34) which is the ANSI code for the double quote.
Finally we passed the following SQL code to call the stored procedure
sp_PhoneSearchLike "o''reilly","Surname"
It now works.
Thanks loads for all your help.
RnR
> This message is in MIME format. Since your mail reader does not
understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C23224.7D3741E0
Content-Type: text/plain; charset="iso-8859-1"
RnR,
replace double quotes with single quotes, where you need to include a
quote
in the string use two single quotes.
this technique is not restricted to stored procedures - use it whenever
you
need to use a quote in a variable\column of character datatypes.
eg.
SELECT @SQLStatement = 'SELECT * FROM Tbl_Directory '
+ 'WHERE ' + @SrchCrit + ' LIKE ''' + @SrchTxt + '%'''
ORDER BY '
+ @SrchCrit + ' ASC'
-----Original Message-----
From: Aruna.Koya@e... [mailto:Aruna.Koya@e...]
Sent: 22 July 2002 14:28
To: sql language
Cc: Stephen.Lewis@e...
Subject: [sql_language] Syntax Error:Apostrophe in strored procedure.
Heres my stored procedure:
CREATE PROCEDURE sp_PhoneSearchLike
(@SrchTxt varchar(50),
@SrchCrit varchar(50))
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT * FROM Tbl_Directory "
+ "WHERE " + @SrchCrit + " LIKE '" + @SrchTxt + "%' ORDER
BY "
+ @SrchCrit + " ASC"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
When I execute the following command ( I have already replaced the single
apostrophe with double apostrophe)
sp_PhoneSearchLike 'o''re','Surname'
I get the following error
"Incorrect syntax near 're'"
Any ideas as to what is happening
Thanks in advance
RnR
This message may contain privileged or confidential information.
If you receive this message in error please return it to the sender and
delete all copies - thankyou.
--- Change your mail options at http://p2p.wrox.com/manager.asp or to
unsubscribe send a blank email to
*********************************************************************
This message and any attachments are confidential and should only be
read by those to whom they are addressed. If you are not the intended
recipient, please contact us, delete the message from your computer
and destroy any copies. Any distribution or copying without our prior
permission is prohibited.
The Achilles Group does not accept legal responsibility for the
contents of this message. The recipient is responsible for verifying
its authenticity before acting on the contents. Any views or opinions
presented are solely those of the author and do not necessarily
represent those of the Achilles Group.
This email has been scanned by MAILsweeper and Sophos Anti-Virus
products. However, The Achilles Group will have no liability for any
viruses contained in this email or any attachment with this email.
*********************************************************************
------_=_NextPart_001_01C23224.7D3741E0
Content-Type: text/html; charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Syntax Error:Apostrophe in strored procedure.</TITLE>
<META content="MSHTML 6.00.2715.400" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=637463808-23072002><FONT face=Arial color=#0000ff
size=2>RnR,</FONT></SPAN></DIV>
<DIV><SPAN class=637463808-23072002><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=637463808-23072002><FONT face=Arial color=#0000ff
size=2>replace double quotes with single quotes, where you need to
include a
quote in the string use two single quotes.</FONT></SPAN></DIV>
<DIV><SPAN class=637463808-23072002><FONT face=Arial color=#0000ff
size=2>this
technique is not restricted to stored procedures - use it whenever you
need to
use a quote in a variable\column of character
datatypes.</FONT></SPAN></DIV>
<DIV><SPAN class=637463808-23072002></SPAN> </DIV>
<DIV><SPAN class=637463808-23072002><FONT face=Arial color=#0000ff
size=2>eg.</FONT></SPAN></DIV>
<DIV><SPAN class=637463808-23072002><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=637463808-23072002><FONT face=Arial><FONT
color=#0000ff><FONT
size=2>SELECT @SQLStatement = 'SELECT * FROM
Tbl_Directory ' <BR></FONT> &
nbsp; <FONT
size=2> + 'WHERE ' +
@SrchCrit
+ ' LIKE ''' + @SrchTxt + '%''' ORDER
BY '</FONT> <BR>
<FONT
size=2> + @SrchCrit + '
ASC'</FONT></FONT><BR></FONT></SPAN></DIV>
<DIV><SPAN class=637463808-23072002><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
Aruna.Koya@e...
[mailto:Aruna.Koya@e...]<BR><B>Sent:</B> 22 July 2002
14:28<BR><B>To:</B> sql language<BR><B>Cc:</B>
Stephen.Lewis@e...<BR><B>Subject:</B> [sql_language] Syntax
Error:Apostrophe in strored procedure.<BR><BR></FONT></DIV>
<P><FONT size=2>Heres my stored procedure:</FONT> </P>
<P><FONT size=2>CREATE PROCEDURE sp_PhoneSearchLike</FONT>
<BR><FONT size=2> (@SrchTxt varchar(50),</FONT> <BR><FONT
size=2>
@SrchCrit varchar(50))</FONT> <BR><FONT size=2>AS</FONT> <BR><FONT
size=2> -- Create a variable @SQLStatement</FONT>
<BR><FONT
size=2> DECLARE @SQLStatement varchar(255)</FONT>
<BR><FONT
size=2> </FONT><BR><FONT size=2> --
Enter
the dynamic SQL statement into the</FONT> <BR><FONT
size=2>
-- variable @SQLStatement</FONT> <BR><FONT size=2>
SELECT
@SQLStatement = "SELECT * FROM Tbl_Directory "
</FONT><BR> <FONT
size=2> + "WHERE " +
@SrchCrit + "
LIKE '" + @SrchTxt + "%' ORDER BY "</FONT>
<BR> <FONT
size=2> + @SrchCrit + "
ASC"</FONT>
<BR> <FONT
size=2> </FONT><BR><FONT
size=2> </FONT><BR><FONT size=2> --
Execute the SQL statement</FONT> <BR><FONT size=2>
EXEC(@SQLStatement)</FONT> <BR><FONT size=2>GO</FONT> </P>
<P><FONT size=2>When I execute the following command ( I have already
replaced
the single apostrophe with double apostrophe)</FONT> </P>
<P><FONT size=2>sp_PhoneSearchLike 'o''re','Surname'</FONT> </P>
<P><FONT size=2>I get the following error</FONT> </P>
<P><FONT size=2>"Incorrect syntax near 're'"</FONT> </P>
<P><FONT size=2>Any ideas as to what is happening</FONT> </P>
<P><FONT size=2>Thanks in advance</FONT> <BR><FONT size=2>RnR</FONT>
</P><CODE><FONT size=3><BR><BR>This message may contain privileged or
confidential information. <BR>If you receive this message in error
please
return it to the sender and delete all copies -
thankyou.<BR></FONT></CODE>---
Change your mail options at http://p2p.wrox.com/manager.asp or to
unsubscribe
send a blank email to
</BLOCKQUOTE><CODE><FONT SIZE=3><BR>
<BR>
*********************************************************************<BR>
This message and any attachments are confidential and should only be<BR>
read by those to whom they are addressed. If you are not the intended<BR>
recipient, please contact us, delete the message from your computer <BR>
and destroy any copies. Any distribution or copying without our prior<BR>
permission is prohibited. <BR>
<BR>
The Achilles Group does not accept legal responsibility for the <BR>
contents of this message. The recipient is responsible for verifying <BR>
its authenticity before acting on the contents. Any views or opinions <BR>
presented are solely those of the author and do not necessarily <BR>
represent those of the Achilles Group.<BR>
<BR>
This email has been scanned by MAILsweeper and Sophos Anti-Virus <BR>
products. However, The Achilles Group will have no liability for any <BR>
viruses contained in this email or any attachment with this email.<BR>
*********************************************************************<BR>
</FONT></CODE>
</BODY></HTML>
------_=_NextPart_001_01C23224.7D3741E0--
View Cart
