Professional Web Applications Themes

Append Variable - Microsoft SQL / MS SQL Server

Friends, please, Could you help me? I have the next storeprocedure create proc miProc sWhere varchar(50) select a.code, b.description from table1 a, table2 b where a.code=b.code + sWhere When I run this with exec miProc " and a.code=321 " the results it's (0 row(s) affected), Please,where is my error? How I can append sWhere at end of the select sentence? Thanks in advance and I'm Sorry by my english CZ...

Sponsored Links
  1. #1

    Default Append Variable

    Friends, please, Could you help me?
    I have the next storeprocedure

    create proc miProc sWhere varchar(50)

    select a.code, b.description
    from table1 a, table2 b
    where a.code=b.code + sWhere

    When I run this with

    exec miProc " and a.code=321 "

    the results it's (0 row(s) affected),
    Please,where is my error? How I can append sWhere at end of the select
    sentence?

    Thanks in advance and I'm Sorry by my english

    CZ


    Sponsored Links
    CZ Guest

  2. #2

    Default Re: Append Variable

    do following in stored procedure

    declare SelectStr as varchar(1000)

    set SelectStr = 'select a.code, b.description from table1 a, table2 b where
    a.code=b.code' + Where
    exec(SelectStr)


    "CZ" <com.pe> wrote in message
    news:phx.gbl... 


    Mohanjit Guest

  3. #3

    Default Re: Append Variable

    You need to use dynamic SQL to pass in a where clause like that. E.g.

    EXEC('SELECT a.code, b.description FROM table1 a, table2 b WHERE a.code =
    b.code ' + sWhere)

    However, please be aware that this reflects poor design, opens your system
    up to SQL injection, and demonstrates that you are not using stored
    procedures correctly (you may as well construct that entire SQL statement on
    the client). Please read
    http://www.algonet.se/~sommar/dynamic_sql.html#Condition and
    http://www.algonet.se/~sommar/dyn-search.html





    "CZ" <com.pe> wrote in message
    news:phx.gbl... 


    Aaron Guest

  4. #4

    Default Append Variable



    exec ('select a.code, b.description
    from table1 a, table2 b
    where a.code=b.code ' + sWhere)

    But be careful about what you allow in where as you could
    end up with a security hole.

     
    of the select 
    Nigel Guest

  5. #5

    Default Re: Append Variable

    Thanks by your answers and advices, it's very important for me.

    CZ


    CZ Guest

Similar Threads

  1. Append to XML
    By jgiotta in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 16th, 01:31 PM
  2. XML append DOM in other DOM
    By mik77 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: June 14th, 02:31 PM
  3. PDF Append
    By Roland_Verhaaf@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 2
    Last Post: April 2nd, 09:46 AM
  4. XML append node without DOM
    By DrewM in forum PHP Development
    Replies: 2
    Last Post: October 16th, 10:04 PM
  5. how to append two Lists together?
    By manno in forum Macromedia Director Lingo
    Replies: 0
    Last Post: August 22nd, 02:01 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