Professional Web Applications Themes

Dynamically building up a WHERE clause - Microsoft SQL / MS SQL Server

I'm relativley new to SQL Server 2000 DB's, and come from an Oracle background. I need to dynamically produce the WHERE clause dependent on parameters being passed into a stored procedure. Can I do this? If so, how?...

  1. #1

    Default Dynamically building up a WHERE clause

    I'm relativley new to SQL Server 2000 DB's, and come from
    an Oracle background.

    I need to dynamically produce the WHERE clause dependent
    on parameters being passed into a stored procedure.

    Can I do this? If so, how?
    Beverley Guest

  2. #2

    Default Re: Dynamically building up a WHERE clause

    Beverley Brindle wrote: 

    You will find this article: http://www.algonet.se/~sommar/dyn-search.html,
    along with the rest of the articles on Erland's site, extremely useful.

    Bob Barrows


    Bob Guest

  3. #3

    Default Re: Dynamically building up a WHERE clause

    You basically have two options. You can use either EXECUTE or sp_executesql
    which is prefered way because it is more likely that SQL Server will reuse
    execution plan when you use sp_executesql. If you bult realy big dynamic SQL
    statement (say ower 8K) you'll have to use EXECUTE bacause sp_executesql is
    limited to 8K string, and you can't concatenate two strings into one and
    pass this to sp_executesql. You can do that with EXECUTE.


    declare orderid int
    set orderid = 10249
    EXECUTE ('SELECT * FROM Orders WHERE OrderID = ''' + orderid + '''')



    execute sp_executesql
    N'SELECT * FROM Orders WHERE OrderId=orderid',
    N'orderid int',
    orderid = orderid


    --
    Regards,

    Tomislav Kralj
    tel.hr

    "Beverley Brindle" <co.uk> wrote in message
    news:08a701c34c4b$3ff317e0$gbl... 


    Tomislav Guest

Similar Threads

  1. Dynamically Building Controls in ASP.net
    By D.Dark in forum ASP.NET Building Controls
    Replies: 2
    Last Post: November 20th, 02:36 PM
  2. dynamically building template columns
    By jonefer in forum ASP.NET Building Controls
    Replies: 4
    Last Post: May 26th, 06:08 PM
  3. Building Query criteria dynamically based on Form inputs
    By Beards247 in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: August 2nd, 06:59 AM
  4. Dynamically Building Property Values for Designer
    By paul reed in forum ASP.NET Building Controls
    Replies: 8
    Last Post: February 16th, 09:15 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