Building and executing SQL Query dynamically, best practices

Discussion in 'ASP .Net' started by =?Utf-8?B?V2ViTWF0cml4?=, Feb 14, 2006.

  1. Hello,

    I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
    users have ability to construct their own select criteria (for a where
    clause). I already developed a user control where user can select field name,
    operator (equals, like, begins with, etc) and value of the search criteria.
    This control can already build where clause part. Now I am at the point,
    where I need to execute SQL query, but I would really like to be able to use
    stored procedures.

    As far as I know, I have 2 options: (1) Pass in “where clause†string to SP
    (2) Execute SQL Query inside ASP.NET

    Are there any other options? If anyone ever had to implement something
    similar, I want to hear your comments. Thanks
    =?Utf-8?B?V2ViTWF0cml4?=, Feb 14, 2006
    #1
    1. Advertising

  2. Hi,

    "WebMatrix" <> wrote in message
    news:...
    > Hello,
    >
    > I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
    > users have ability to construct their own select criteria (for a where
    > clause). I already developed a user control where user can select field
    > name,
    > operator (equals, like, begins with, etc) and value of the search
    > criteria.
    > This control can already build where clause part. Now I am at the point,
    > where I need to execute SQL query, but I would really like to be able to
    > use
    > stored procedures.


    You cannot use a SP for this, the query will change all the time therefore
    the engine cannot precalculate the execution path of it. you will have to
    submit it as CommandType = Text

    One warning I give you is taht you have to be careful with SQL injection,
    the query can be injected both in the select as well as in the where clauses



    --
    Ignacio Machin,
    ignacio.machin AT dot.state.fl.us
    Florida Department Of Transportation
    Ignacio Machin \( .NET/ C# MVP \), Feb 14, 2006
    #2
    1. Advertising

  3. Sure you can. stored procedures can dynamically execute sql via a number of
    functions, include exec and sp_execute.You do lose out on some of the sproc
    benefits though (such as cached execution plans)

    I'm not sure which I'd use...the difference between the two at the point you
    are at is pretty minimal...

    Here's an *must read* guide for anyone writing dynamic sql :)

    http://www.sommarskog.se/dynamic_sql.html

    Karl
    --
    http://www.openmymind.net/
    http://www.fuelindustries.com/


    "Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
    in message news:uI$...
    > Hi,
    >
    > "WebMatrix" <> wrote in message
    > news:...
    >> Hello,
    >>
    >> I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
    >> users have ability to construct their own select criteria (for a where
    >> clause). I already developed a user control where user can select field
    >> name,
    >> operator (equals, like, begins with, etc) and value of the search
    >> criteria.
    >> This control can already build where clause part. Now I am at the point,
    >> where I need to execute SQL query, but I would really like to be able to
    >> use
    >> stored procedures.

    >
    > You cannot use a SP for this, the query will change all the time therefore
    > the engine cannot precalculate the execution path of it. you will have to
    > submit it as CommandType = Text
    >
    > One warning I give you is taht you have to be careful with SQL injection,
    > the query can be injected both in the select as well as in the where
    > clauses
    >
    >
    >
    > --
    > Ignacio Machin,
    > ignacio.machin AT dot.state.fl.us
    > Florida Department Of Transportation
    >
    Karl Seguin [MVP], Feb 14, 2006
    #3
  4. =?Utf-8?B?V2ViTWF0cml4?=

    sloan Guest

    Check this article:

    http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp

    (which I wrote)

    You're going to get some "dynamic sql is ok", but I don't like it. Please,
    I know the arguments, and don't need another round of it. (or others
    reading).

    You can use your select screen to create the Xml, which gets passed into the
    procedure.

    ...

    If you're using Access. then you have no choice, but to build the SQL.

    If you're using Oracle, the last time I used Oracle 9, it was very very
    cumbersome to use their XML model.

    ...



    "WebMatrix" <> wrote in message
    news:...
    > Hello,
    >
    > I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
    > users have ability to construct their own select criteria (for a where
    > clause). I already developed a user control where user can select field

    name,
    > operator (equals, like, begins with, etc) and value of the search

    criteria.
    > This control can already build where clause part. Now I am at the point,
    > where I need to execute SQL query, but I would really like to be able to

    use
    > stored procedures.
    >
    > As far as I know, I have 2 options: (1) Pass in "where clause" string to

    SP
    > (2) Execute SQL Query inside ASP.NET
    >
    > Are there any other options? If anyone ever had to implement something
    > similar, I want to hear your comments. Thanks
    >
    sloan, Feb 14, 2006
    #4
  5. =?Utf-8?B?V2ViTWF0cml4?=

    Chip Guest

    You can definitely create dynamic SQL in an SPROC. And I don't beleive you
    do suffer from the caching issue (an urban myth these days as SQL no longer
    "precompiles" procs, but instead stores execution plans for all queries).
    Here is a working example.

    create proc RepMetaStateBookList
    @year char(5),
    @range varchar(10)
    as
    declare @sql varchar(2000)
    set @sql = '
    select top 100
    Author,
    Title,
    count(*) Lists
    from list l
    join list_detail d on l.listID = d.listID
    join book b on d.bookID = b.bookID
    join author a on b.authorID = a.authorID
    where ( list_name like ''%:%' + @year + '%'' )
    and left(list_name, 2) in (select code from arlist_dev..state)
    ' +
    case
    when @range = 'PK-2' then ' and cast(level_atos as float) <= 2.9 '
    when @range = '3-5' then ' and cast(level_atos as float) between 3.0 and
    5.9 '
    when @range = '6-8' then ' and cast(level_atos as float) between 6.0 and
    8.9 '
    when @range = '9-12' then ' and cast(level_atos as float) >= 9.0 '
    when @range = 'ALL' then ''
    end
    + '
    group by author, title
    having count(*) > 1
    order by count(*) desc, author, title '

    exec ( @sql )



    "WebMatrix" <> wrote in message
    news:...
    > Hello,
    >
    > I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
    > users have ability to construct their own select criteria (for a where
    > clause). I already developed a user control where user can select field
    > name,
    > operator (equals, like, begins with, etc) and value of the search
    > criteria.
    > This control can already build where clause part. Now I am at the point,
    > where I need to execute SQL query, but I would really like to be able to
    > use
    > stored procedures.
    >
    > As far as I know, I have 2 options: (1) Pass in "where clause" string to
    > SP
    > (2) Execute SQL Query inside ASP.NET
    >
    > Are there any other options? If anyone ever had to implement something
    > similar, I want to hear your comments. Thanks
    >
    Chip, Feb 14, 2006
    #5
  6. Hi,

    "Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
    net> wrote in message news:%...
    > Sure you can. stored procedures can dynamically execute sql via a number
    > of functions, include exec and sp_execute.You do lose out on some of the
    > sproc benefits though (such as cached execution plans)
    >


    IMO it would be the same, just adding another layer ( has to invoke the
    SP ).


    > I'm not sure which I'd use...the difference between the two at the point
    > you are at is pretty minimal...
    >
    > Here's an *must read* guide for anyone writing dynamic sql :)
    >
    > http://www.sommarskog.se/dynamic_sql.html


    Very good article, thanks for the link



    --
    Ignacio Machin,
    ignacio.machin AT dot.state.fl.us
    Florida Department Of Transportation
    Ignacio Machin \( .NET/ C# MVP \), Feb 14, 2006
    #6
  7. =?Utf-8?B?V2ViTWF0cml4?=

    Vijay14s

    Joined:
    May 15, 2008
    Messages:
    1
    Building SQL query dynamically

    I am developing a power search page in asp.net 2.0 and sql server 200.
    Can you provide the sample code that you implemented to collect user selected search criteria[field name, operator (equals, like, begins with, etc)]
    Very thank full if provided ASAP


    Vijay14s, May 15, 2008
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Russ

    SQL Query good practices?

    Russ, Jan 14, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    380
    Curt_C [MVP]
    Jan 14, 2004
  2. Anonymous
    Replies:
    0
    Views:
    1,459
    Anonymous
    Oct 13, 2005
  3. Anton Ishmurzin
    Replies:
    5
    Views:
    506
    Anton Ishmurzin
    Apr 7, 2004
  4. Usenet User
    Replies:
    4
    Views:
    563
    Usenet User
    Sep 22, 2008
  5. Chicken McNuggets

    Best book on C gotchas and best practices?

    Chicken McNuggets, Jul 31, 2013, in forum: C Programming
    Replies:
    9
    Views:
    265
    Fred J. Tydeman
    Aug 5, 2013
Loading...

Share This Page