compiling stored proc with sqlcmd vrs management studio 1

[ddiamond]

I noticed that not all of my stored procs behaved the same when compiled using sqlcmd instead of management studio. The culprit appears to be how QUOTED_IDENTIFIER is defaulted. Management studio defaults it to ON. Sqlcmd defaults it to OFF.

So I'm now tempted to explicitely put

SET QUOTED_IDENTIFIER ON
GO

in front of all of my stored procs. Is there any downside to this approach?

 

[gmmastros]

In my opinion, it would be better to follow the SQL-92 rules regarding quotation marks.

Simply put.... don't ever use the quote symbol ([!]"[/!]) in your code. If you need to delimit objects (because of reserved words or spaces in the name) then you should use square brackets. Single-quotes should be reserved for hard coded strings.

Ex:

[tt]
Select [column] From

where [column] = 'Hard coded string'
[/tt]

If you follow the sql-92 rules, it will not matter whether you have quoted_identifier on or off.


- George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh, yeah.... If you want to show one of your procs where the quoted_identifier setting is causing you a problem, post it here and I will show you how to "fix" it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ddiamond]

This is one of the stored procs that require quoted_identifier on. Otherwise the insert statements generate a run-time error.

CREATE PROCEDURE dbo.sp_ErrLogInsert (
  @ErrorCode        VARCHAR(5),
  @Keys             XML,
  @TableName        VARCHAR(50),
  @Source           VARCHAR(50) )
AS
BEGIN

  DECLARE @ProductCode      VARCHAR(6)
  DECLARE @FeedDate         VARCHAR(12)
  DECLARE @TransactionId    INT
  DECLARE @TransactionSeq   INT

  IF @Source = 'Flat01'
    IF @TableName IN ('TransactionMaster', 'Deduction', 'NameInvolvement')
      BEGIN
  
        /*
        <Key>
          <ProductCode>E&O</ProductCode>
          <FeedDate>200911051127</FeedDate>
          <TransactionId>25</TransactionId>
          <TransactionSeq>1</TransactionSeq>
        </Key>
        */
  
        INSERT INTO Flat01ErrLog (
          ProductCode,
          FeedDate,
          TransactionId,
          Source,
          ErrCode,
          ErrMsg )
        SELECT
          row.value('(./ProductCode)[1]'  , 'VARCHAR(6)')  AS ProductCode,
          row.value('(./FeedDate)[1]'     , 'VARCHAR(12)') AS FeedDate,
          row.value('(./TransactionId)[1]', 'INT')         AS TransactionId,
          'Flat01MasterWK'                                 AS Source,
          @ErrorCode                                       AS ErrCode,
          ( SELECT ErrMsg FROM Flat01ErrCode WHERE ErrCode = @ErrorCode ) AS ErrMsg
        FROM @Keys.nodes('/Key') AS T(row)
      END
    ELSE IF @TableName IN ('TransactionDetail', 'Deductible', 'Limit', 'Premium', 'TransactionExtDetail')
      BEGIN
        /*
        <Key>
          <ProductCode>E&O</ProductCode>
          <FeedDate>200911051127</FeedDate>
          <TransactionId>25</TransactionId>
          <TransactionSeq>1</TransactionSeq>
          <Section>MED</Section>
          <Subsection>1MM</Subsection>
          <LocationNumber>1</LocationNumber>
        </Key>
        */

        INSERT INTO Flat01ErrLog (
          ProductCode,
          FeedDate,
          TransactionId,
          Section,
          Subsection,
          LocationNumber,
          Source,
          ErrCode,
          ErrMsg )
        SELECT
          row.value('(./ProductCode)[1]'   , 'VARCHAR(6)')  AS ProductCode,
          row.value('(./FeedDate)[1]'      , 'VARCHAR(12)') AS FeedDate,
          row.value('(./TransactionId)[1]' , 'INT')         AS TransactionId,
          row.value('(./Section)[1]'       , 'VARCHAR(3)')  AS Section,
          row.value('(./Subsection)[1]'    , 'VARCHAR(3)')  AS Subsection,
          row.value('(./LocationNumber)[1]', 'INT')         AS LocationNumber,
          'Flat01DetailWK'                                  AS Source,
          @ErrorCode                                        AS ErrCode,
          ( SELECT ErrMsg FROM Flat01ErrCode WHERE ErrCode = @ErrorCode ) AS ErrMsg
        FROM @Keys.nodes('/Key') AS T(row)
      
      END
END
GO

 

[markros]

I don't see anything in this SP requiring usage of quoted identifier.

PluralSight Learning Library

 

[ddiamond]

Markros,

I would agree that there is nothing obvious requiring usage of quoted identifier. Never the less, it is required for some reason. Otherwise I get the following error message:

INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

The following link gives more details on the problem:

http://msdn.microsoft.com/en-us/library/ms174393(SQL.90).aspx

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

I'm not sure if that quote applies. I am inserting into a table with indexes and I do have computed fields, but the index is not on a computed field.

 

[ddiamond]

The following link suggests that the quoted_identifier must be on when using certain xml methods such as nodes an value.

http://vadivel.blogspot.com/2005/07/xml-data-type-and-quoted-identifier.html

Can anyone confirm this?