HOW TO SET SP - SET QUANTIFIED_IDENTIFIER OFF PARAMETER

Muzaffer 21 Reputation points
2022-10-06T08:17:00.333+00:00

Hi

Among about 25000 SPs, 1000 of them were generated with the set quantified identifier OFF identifier and i wanted to change this parameter to ON. I can't filter these stored procedures to export as script and have a concern about breaking the script format at the point of altering SPs from module definitions
Do you have any suggestion or trick to change these parameters on selected Stored Procedures.

Regards

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,743 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. NikoXu-msft 1,911 Reputation points
    2022-10-07T02:32:54.037+00:00

    Hi @Muzaffer ,

    After consulting a lot of information, I finally found an article that might help you , please see:
    https://solutioncenter.apexsql.com/changing-sql-stored-procedures-and-functions-parameters/

    Best regards
    Niko

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments
  2. Muzaffer 21 Reputation points
    2022-10-07T09:14:28.807+00:00

    hi @NikoXu-msft

    Thanks for your response 248502-1.jpgactually the property parameter that I want to change

    0 comments
  3. Tom Phillips 17,716 Reputation points
    2022-10-07T12:33:57.38+00:00

    That setting does not affect the stored proc, it affects the script you are running to CREATE the proc. There is no reason to change it.

    See:
    https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-ver16

    When SET QUOTED_IDENTIFIER is ON (default), identifiers can be delimited by double quotation marks (" "), and literals must be delimited by single quotation marks (' '). All strings delimited by double quotation marks are interpreted as object identifiers.

    SET QUOTED_IDENTIFIER takes effect at Transact-SQL parse-time and only affects parsing, not query optimization or query execution.