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.

11
  • 1
    When happens when you SET and then ALTER? Sep 23, 2014 at 17:22
  • @AaronBertrand wouldn't I have to basically redeclare the procedure in that case? Sep 23, 2014 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. Sep 23, 2014 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 Sep 24, 2014 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? Sep 24, 2014 at 15:45

2 Answers 2

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

2
  • 1
    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. Aug 10, 2015 at 14:44
  • fair point... SSDT v12.0.50730
    – Bill Anton
    Aug 11, 2015 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 and acknowledge you have read our privacy policy.

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