Professional Web Applications Themes

Dynamic Query and Quotes - Microsoft SQL / MS SQL Server

i'm creating a Dynamic Query inside one of my stored procedures, and i got the following question... if i use quotes for setting my Dynamic Query inside a string, how do i set a text inside the Dynamic Query: EX: Select Sql = 'Select * From Teams where Category = ( i want to use here quotes for setting the category, like let's say 'A', but if i use quotes it will be used for closing or opening the main sql statement) ' What can i do to avoid this Thanks. Alex....

  1. #1

    Default Dynamic Query and Quotes

    i'm creating a Dynamic Query inside one of my stored procedures, and i got
    the following question... if i use quotes for setting my Dynamic Query
    inside a string, how do i set a text inside the Dynamic Query:
    EX:
    Select Sql = 'Select * From Teams where Category = ( i want to use here
    quotes for setting the category, like let's say 'A', but if i use quotes it
    will be used for closing or opening the main sql statement) '

    What can i do to avoid this

    Thanks.
    Alex.


    Ale Guest

  2. #2

    Default Re: Dynamic Query and Quotes

    double up the single quotes.
    Ex:
    Select Sql = 'Select * From Teams where Category = ''ALFKI'''

    --
    -Vishal

    "Ale K." <com> wrote in message
    news:phx.gbl... 
    it 


    Vishal Guest

  3. #3

    Default Re: Dynamic Query and Quotes

    Ale K. (com) writes: 

    Look at http://www.algonet.se/~sommar/dynamic_sql.html#good_practices for
    some tips.

    --
    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

  4. #4

    Default Re: Dynamic Query and Quotes

    Use a parameterized query (sp_executesql).

    Example:

    declare category varchar(100)
    set category = 'Joe''s Category'
    exec sp_executesql
    N'Select * From Teams where Category = cat',
    N'cat varchar(100)',
    category


    "Ale K." <com> wrote in message
    news:phx.gbl... 
    it 


    Anthony Guest

  5. #5

    Default Re: Dynamic Query and Quotes

    Hi Alek/Vishal,
    Query can also be written as

    Select Sql = 'Select * From Teams where Category = '+
    CHAR(39)+ 'ALFKI' + CHAR(39)

    regards
    Hari Sharma, NIIT Technologies, India

    "Vishal Parkar" <com> wrote in message news:<uyEq#phx.gbl>... 
    > it [/ref]
    hkvats_1999@yahoo.com Guest

Similar Threads

  1. Using Quotes in a Query
    By gmtjr in forum Coldfusion Database Access
    Replies: 4
    Last Post: November 5th, 01:32 AM
  2. CFMX7 and dynamic query --Need to maintain single quotes
    By DixieGal in forum Coldfusion Database Access
    Replies: 4
    Last Post: October 7th, 11:32 PM
  3. Smart Quotes in Dynamic text field
    By bwillidesign webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: January 30th, 12:55 AM
  4. Dynamic SQL: INSERT single and double quotes
    By Tim in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 21st, 06:12 PM

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