1

I have to execute a stored procedure which expects a single quote (') in parameter. I'm using this, but this isn't working:

exec sp_promo_db_list_generic_search_by_sales_order 
        '1','1', 'logo_name','Hamley's', 'N','N''

I am getting the error:

Incorrect syntax near 's'.

I am expecting that the stored procedures accepts ' character in the parameter. I've tried using the escape character('Hamley/'s') still it isn't working.

Zhorov
  • 28,486
  • 6
  • 27
  • 52
Chaitanya Galande
  • 11
  • 3
  • 1
    You need to [escape](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) the single quote: `exec sp_promo_db_list_generic_search_by_sales_order '1','1', 'logo_name', 'Hamley''s', 'N','N'`. And it's not clear what are the actual values of the last two parameters. – Zhorov Feb 13 '23 at 07:51
  • Thanks ,I tried using escaping character by doubling the quotes but it isn't working.Same goes for using SET QUOTED_IDENTIFIER OFF; – Chaitanya Galande Feb 13 '23 at 08:02
  • 1
    You need to escape only the single quotes inside the string literal (`'Hamley''s'`, not `''Hamley''s''`). – Zhorov Feb 13 '23 at 08:21
  • Yes, tried this only. Still not working. – Chaitanya Galande Feb 13 '23 at 08:53
  • Repeat the same character twice: '' instead ' – Bogdan Sahlean Feb 13 '23 at 09:51
  • @ChaitanyaGalande can you share your SQL after making the changes Bogdan suggested? Running this very simple query worked for me `SELECT 'Hamley''s'` so keen to see what you've changed – Anthony Norwood Feb 13 '23 at 10:01
  • Considernig that typing in SELECT 'Hamley''s' into SSMS works just fine, is there something in the SP that's causing the error? – JonTout Feb 13 '23 at 10:13
  • @Antony , the execute statement after the changes was - exec sp_promo_db_list_generic_search_by_sales_order '1','1', 'logo_name','Hamley''s', 'N','N' – Chaitanya Galande Feb 13 '23 at 11:44
  • @John actually this works only for the select statemnt ,for using it in paramter in a stored procedure (which expects more than one parameter) it doesn't works – Chaitanya Galande Feb 13 '23 at 11:45

1 Answers1

0

If you are using single quotes in ' ' then Always add one more single quotes.

    exec sp_promo_db_list_generic_search_by_sales_order 
    '1','1', 'logo_name','Hamley''s', 'N','N'
B.Muthamizhselvi
  • 642
  • 4
  • 13
  • I tried the same ,for some reason it's not working. I'm getting the same error Incorrect syntax near 's'. for exec sp_promo_db_list_generic_search_by_sales_order '1','1', 'logo_name','Hamley''s', 'N','N' – Chaitanya Galande Feb 13 '23 at 11:47
  • can you post the stored proc script? – JonTout Feb 13 '23 at 13:12
  • SET QUERY = 'INSERT INTO #TEMP SELECT * FROM sales_orders where sales_orders.trans_flag = ''A'' AND ' + as_column_name + ' like ' + '''%' + as_search_value + '%'' ' + as_closed_order_condition + as_sub_order_condition + as_estimate_no_condition + 'ORDER BY trans_date desc,trans_no desc ' EXECUTE(QUERY); Here , as_search_value is Hamley's . I've removed the @ here because stackoverflow wasn't allowing the @ sign in comment – Chaitanya Galande Feb 14 '23 at 06:42