How do I do quotes within quotes in TSQL

Question

I have a case statement that will set a varchar variable @stored_proc_name to the name of a procedure that I want to call later on. One of the procedures I want to call takes a varchar as an argument (and also an int). How do I do this? Currently what I have is this

SELECT @stored_proc_name = CASE @p_process_name
      WHEN 'misdate'               THEN 'findb..sp_cycle_date_daily' 
      WHEN 'balpremmis'            THEN 'findb..pc_bal_writ_prem'
      WHEN 'ursctl'                THEN 'MIS_feeds..pc_mis_update_feed_control "URSPO", 1'
      ELSE NULL
END

EXECUTE @stored_proc_name

The last one is my procedure that takes arguments, where "URSPO" should be the first, and 1 is the int. It is running on SQL server 2k8 (I think I remember some difference with single vs. double quotes between this and older versions). Is what I have correct? I admit that I haven't tested it yet, but it is a part of a huge process that I really don't want to kick off right now.

Thank for any help!

Answer 1

To escape a single quote inside a string literal, use a double quote like below. See the Constants section in the BOL for more information:

select ' '' ' -- creates a string containing a space, a single quote, and another space

In your example, this would be the string:

'MIS_feeds..pc_mis_update_feed_control ''URSPO'', 1'

If you were going to use this string in a LIKE expression, you might want to refer to this question.

And if you were going to build a string to be a SQL identifier, you might want to escape it using the QUOTENAME function.

Answer 2

You can use the code below:

set quoted_identifier off;
Select "'Hi'"

quoted_identifiere will enable you to use ", instead of ';