Sproc parameter: escaping ' character

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Marina (someone_at_nospam.com)
Date: 04/14/04 

Date: Wed, 14 Apr 2004 09:56:43 -0400

Hi,

Let's say I'm trying to execute a stored procedure by calling 'Exec'. If an
argument to the sproc has a single quote (which is doubled up), then the
whole thing dies with a syntax error. Consider this:

Exec MySproc 'arg1','here''s arg2','arg3'

For what it's worth, the syntax highlighting is just fine in query analyzer.
The message I receive is: "Incorrect syntax near 's'." and also
"Unclosed quotation mark before the character string ''."

If I just do : Exec MySproc 'arg1','heres arg2','arg3'
Then everything is fine.

So why doesn't escaping the single quote work in this case, where as it does
for string literals in regular queries?

What's the proper way to escape this?

Relevant Pages

  • RE: [Info-ingres] Migration
    ... I have a syntax problem with IngresII ... statements are using the sql string delimiter single quote instead of the ... Also the copy statements are in SQL syntax. ...
    (comp.databases.ingres)
  • Re: exec statement Syntax Error on string pulled from MySQL
    ... and trying to run exec on it, and it keeps giving me a syntax error, ... print 'greg' ... SyntaxError: invalid syntax ... exec code.replacein context ...
    (comp.lang.python)
  • Re: Macro to VB
    ... ampersand is concantenate and a single quote is a comment, ... I intended to use the docmd as a part of an If ... in the If command. ... How do I change the syntax to make this ...
    (microsoft.public.access.modulesdaovba)
  • Re: syntax Check on all stored procedures in a database script
    ... A column was dropped from a table and the list of stored procedures using ... a script or something in SQL Server that will check the syntax on all ... stored procs in the database as a way to verify that nothing borke by ... EXEC P1 0 ...
    (microsoft.public.sqlserver.server)
  • exec statement Syntax Error on string pulled from MySQL
    ... and trying to run exec on it, and it keeps giving me a syntax error, ... print 'greg' ... func = lambda: handle ... SyntaxError: invalid syntax ...
    (comp.lang.python)