1

I'd like to set ANSI NULLs and quoted identifier properties on a stored procedure after it has been created. Is this possible?

For some context, I'm using Visual Studio Database Projects / SSDT, and it appears these values can only be set at the database level, not on the procedure level. However, we're using an existing database with inconsistently created procedures, so I'd like to preserve the current behavior until we can normalize them.

  • 1
    When happens when you SET and then ALTER? – Aaron Bertrand Sep 23 '14 at 17:22
  • @AaronBertrand wouldn't I have to basically redeclare the procedure in that case? – Brandon Linton Sep 23 '14 at 18:23
  • Yes, why is that a problem? Right-click > modify. If you need to do it for many procedures, this is easy to automate. – Aaron Bertrand Sep 23 '14 at 18:29
  • @AaronBertrand it doesn't exactly fit the database project paradigm though where you have a single canonical "create procedure" statement and the SSDT tooling creates the alter when there are modifications for deployment – Brandon Linton Sep 24 '14 at 15:44
  • @AaronBertrand but are you saying it's basically impossible outside of a CREATE / ALTER PROCEDURE statement and setting those properties right before it? – Brandon Linton Sep 24 '14 at 15:45
3

Haven't tested this on other versions of Visual Studio, but with version 12.0.3 (Update4) there is an option to override the ANSI Nulls config at the database-level specification for a single procedure in an SSDT project.

Just right-click the procedure in solution explorer and choose properties...

override ansi nulls for single procedure

  • It should be made clear in this answer that this functionality is more so dependent on SSDT and the particular version of it that is installed, and not specifically on Visual Studio. – Solomon Rutzky Aug 10 '15 at 14:44
  • fair point... SSDT v12.0.50730 – Bill Anton Aug 11 '15 at 12:39
0

To answer the question as it is stated in the title, without regards to the additional detail of wanting to do this via a Visual Studio / SSDT project:

Yes, it is possible to change certain options after creation by simply issuing an ALTER statement. The options that can change for Stored Procedures, at the very least, are:

  • ANSI_NULLS
  • QUOTED_IDENTIFIER
  • WITH RECOMPILE
  • WITH ENCRYPTION
  • WITH EXECUTE AS

For ANSI_NULLS and QUOTED_IDENTIFIER, just issue the appropriate SET statement and then issue the ALTER PROCEDURE statement with the exact same definition that it currently has.

For the WITH... options, you need to start with the current definition, and then add the appropriate WITH option(s), and then run the ALTER on the modified definition (since the WITH clause is part of the object definition, whereas SET session properties are not).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.