Professional Web Applications Themes

Dynamic SQL: INSERT single and double quotes - Microsoft SQL / MS SQL Server

Lookup the expression QUOTENAME , I use it a lot in dynamic SQL. "Bob Barrows" <com> wrote in message news:phx.gbl...  treat  > > >[/ref]...

  1. #1

    Default Re: Dynamic SQL: INSERT single and double quotes

    Lookup the expression QUOTENAME , I use it a lot in dynamic SQL.

    "Bob Barrows" <com> wrote in message
    news:phx.gbl... 
    treat 
    >
    >
    >[/ref]



    Tim Guest

  2. #2

    Default Re: Dynamic SQL: INSERT single and double quotes

    Max West (net) writes: 

    Hmmm.... Well, I don't see the complete design, so I don't know if it
    is flawed or not.
     

    This does not work, because QUOTED_IDENTIFIER is set at compile time.
    It does work, though, if you include the SET statement in the SQL
    string.

    Better though, is to use a function like quotename().

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  3. #3

    Default Re: Dynamic SQL: INSERT single and double quotes

    Erland, Bob,
    Thanks and sorry - here is the full routine:
    DROP TABLE #tmpTable
    DECLARE length1 varchar(25)
    ,length2 varchar(25)
    ,col_id int
    ,execstr varchar(255)

    CREATE TABLE #tmpTable (
    obj_id int
    )
    SET length1 = '1''3" long'
    SET length2 = '2''6" long'
    -- these are actually stored in our db as <1'3" long> and
    <2'6" long>
    -- with single embedded apostrophes/single quotes.
    Parsing each
    -- for <'> and/or <"> on insert, update and delete would
    be bersome.

    SET col_id = 2
    EXEC ('ALTER TABLE #tmpTable ADD col_' + col_id + '
    varchar(25)')
    INSERT #tmpTable VALUES (1, length1)

    SET execstr = 'SET QUOTED_IDENTIFIER ON UPDATE #tmpTable
    SET col_' + LTRIM(RTRIM(STR(col_id))) + ' = ' + '"' +
    length2 + '"'
    SELECT execstr AS 'Update Query'
    EXEC (execstr)
    SELECT * FROM #tmpTable

    I couldn't seem to get Bob's doubled-up apostrophes to
    work. And quotestring() returns leading and trailing
    apostrophes, but it (and any similar that I can think of)
    doesn't cure the issue of embedded apostrophes and double
    quotes... Ideas? Thanks again.
    Max
     
    >
    >Hmmm.... Well, I don't see the complete design, so I[/ref]
    don't know if it 
    >
    >This does not work, because QUOTED_IDENTIFIER is set at[/ref]
    compile time. 
    in the SQL 
    ks.asp 
    Max Guest

  4. #4

    Default Re: Dynamic SQL: INSERT single and double quotes

    Get rid of the QUOTED_IDENTIFIER setting. Don't delimit your column names
    with quotes. If they need to be delimited due to their containing spaces,
    use brackets [] instead.

    Start by hard-coding the SQL Statement you wish to be executed to make sure
    it works. Then do the concatenation to create the variable containing the
    sql statement. Print it! Make sure it corresponds with the hard-coded
    statement. Play with this, and if you can't get it working, show us the
    result of the Print statement along with the code that lead to it.

    Bob Barrows

    Max West wrote: 
    >>
    >> Hmmm.... Well, I don't see the complete design, so I don't know if it
    >> is flawed or not.
    >> 
    >>
    >> This does not work, because QUOTED_IDENTIFIER is set at compile time.
    >> It does work, though, if you include the SET statement in the SQL
    >> string.
    >>
    >> Better though, is to use a function like quotename().
    >>
    >> --
    >> Erland Sommarskog, SQL Server MVP, se
    >>
    >> Books Online for SQL Server SP3 at
    >> http://www.microsoft.com/sql/techinfo/productdoc/2000/boo ks.asp
    >> .[/ref][/ref]



    Bob Guest

  5. #5

    Default Re: Dynamic SQL: INSERT single and double quotes

    This works, in case you haven't fixed it yourself by now:
    DROP TABLE #tmpTable
    DECLARE length1 varchar(25)
    ,length2 varchar(25)
    ,col_id int
    ,execstr varchar(255)

    CREATE TABLE #tmpTable (
    obj_id int
    )
    SET length1 = '1''3" long'
    SET length2 = '2''6" long'
    -- these are actually stored in our db as <1'3" long> and <2'6" long>
    -- with single embedded apostrophes/single quotes.Parsing each
    -- for <'> and/or <"> on insert, update and delete would be bersome.

    SET col_id = 2
    SET execstr ='ALTER TABLE #tmpTable ADD col_' +
    cast(col_id as varchar(10)) + ' varchar(25)'
    print execstr
    EXEC (execstr)
    INSERT #tmpTable VALUES (1, length1)
    SELECT * FROM #tmpTable
    SET execstr = 'UPDATE #tmpTable
    SET col_' + LTRIM(RTRIM(STR(col_id))) + ' = ''' +
    replace(length2,'''','''''') + ''''

    SELECT execstr AS 'Update Query'
    EXEC (execstr)
    SELECT * FROM #tmpTable

    HTH,
    Bob Barrows

    Max West wrote: 
    >>
    >> Hmmm.... Well, I don't see the complete design, so I don't know if it
    >> is flawed or not.
    >> 
    >>
    >> This does not work, because QUOTED_IDENTIFIER is set at compile time.
    >> It does work, though, if you include the SET statement in the SQL
    >> string.
    >>
    >> Better though, is to use a function like quotename().
    >>
    >> --
    >> Erland Sommarskog, SQL Server MVP, se
    >>
    >> Books Online for SQL Server SP3 at
    >> http://www.microsoft.com/sql/techinfo/productdoc/2000/boo ks.asp
    >> .[/ref][/ref]


    Bob Guest

  6. #6

    Default Re: Dynamic SQL: INSERT single and double quotes

    Hey! That works great! Thanks, Bob (and Erland and Tim) -
    this was my first foray here and I'll be following it in
    the future.

    One quick question, Bob, if ou have a minute: in your
    syntax
    SET execstr = 'UPDATE #tmpTable SET col_' + LTRIM(RTRIM
    (STR(col_id))) + ' = ''' + replace
    (length2,'''','''''') + ''''

    you have <' = '''>. When I try to match up and account
    for the apostrophes on my own, I come up with <' = '
    + ''''> which also works. I'm not finding much in the
    doentation on your construct - any hints or ideas
    where I can learn more? Thanks again.
    Max
     
    and <2'6" long> 
    quotes.Parsing each 
    be bersome. [/ref]
    and [/ref]
    would [/ref]
    #tmpTable [/ref]
    + [/ref]
    of) [/ref]
    double [/ref][/ref]
    don't know if it [/ref][/ref]
    at compile time. [/ref][/ref]
    statement in the SQL [/ref][/ref]
    http://www.microsoft.com/sql/techinfo/productdoc/2000/boo
    ks.asp [/ref]
    >
    >
    >.
    >[/ref]
    Max Guest

Similar Threads

  1. Single and Double quotes in SQL
    By Oliver Schnarchendorf in forum PERL Beginners
    Replies: 3
    Last Post: September 19th, 06:00 AM
  2. Single Quotes vs Double Quotes
    By Dave Dash in forum PHP Development
    Replies: 0
    Last Post: September 11th, 02:47 PM
  3. Parsing to insert single quotes
    By Eric in forum PERL Miscellaneous
    Replies: 9
    Last Post: September 10th, 10:33 PM
  4. [PHP] Single Quotes vs Double Quotes
    By Tom Rogers in forum PHP Development
    Replies: 0
    Last Post: September 9th, 04:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139