473,728 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Building and executing SQL Query dynamically, best practices

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

Feb 14 '06 #1
5 4193
Hi,

"WebMatrix" <We*******@disc ussions.microso ft.com> wrote in message
news:02******** *************** ***********@mic rosoft.com...
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
Feb 14 '06 #2
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.mach in AT dot.state.fl.us > wrote
in message news:uI******** ******@tk2msftn gp13.phx.gbl...
Hi,

"WebMatrix" <We*******@disc ussions.microso ft.com> wrote in message
news:02******** *************** ***********@mic rosoft.com...
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

Feb 14 '06 #3
Check this article:

http://www.sqlservercentral.com/colu...terproblem.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" <We*******@disc ussions.microso ft.com> wrote in message
news:02******** *************** ***********@mic rosoft.com...
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

Feb 14 '06 #4
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
"precompile s" procs, but instead stores execution plans for all queries).
Here is a working example.

create proc RepMetaStateBoo kList
@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..sta te)
' +
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" <We*******@disc ussions.microso ft.com> wrote in message
news:02******** *************** ***********@mic rosoft.com...
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

Feb 14 '06 #5
Hi,

"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
net> wrote in message news:%2******** ********@TK2MSF TNGP14.phx.gbl. ..
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
Feb 14 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2462
by: alanstew | last post by:
With the body tag calling out 'window onload', a function with a 'window.open' fails at the 'window.open' line. If I cut out the body tag, the function executes as normal. At first I thought it was the entire function failing, but I tested with alerts and found that it was only the 'window.open' that fails to execute. The function is being called by a link, and I suspected some problem with the body alink/vlink but after cutting that out...
136
9425
by: Matt Kruse | last post by:
http://www.JavascriptToolbox.com/bestpractices/ I started writing this up as a guide for some people who were looking for general tips on how to do things the 'right way' with Javascript. Their code was littered with document.all and eval, for example, and I wanted to create a practical list of best practices that they could easily put to use. The above URL is version 1.0 (draft) that resulted. IMO, it is not a replacement for the FAQ,...
16
12320
by: audleman | last post by:
I'm attempting to build a query that involves a table called "All Students". The query is simply sqlString = "SELECT * FROM All Students" but I get "Syntax error in FROM clause" when I try to run it. I assume this is because my table is two words. How do I get Access to recognize it?
5
2502
by: WebMatrix | last post by:
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...
0
1637
by: roger23 | last post by:
I get this error C:\Program Files\MSBuild\Microsoft\WebDeployment\v8.0\Microsoft.WebDeployment.targets(526,9): error MSB6006: "aspnet_compiler.exe" exited with code 1. at the end of my build step of my web deployment project. it does not give me any error messages to debug. the only place where i can see some problem is this: Dependency "System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a".
3
2168
by: Nathan Gilbert | last post by:
I am wanting to use javascript to select between different *.css files dependent on the user's browser. I am also wanting to generate the html document containing this javascript dynamically using PERL. So far, I have the javascript that does what I want to do, and it works fine as long as the the page was not generated by PERL. My problem is that when the javascript/html is generated by my PERL scripts the javascript never gets...
0
1581
by: uncensored | last post by:
Hi, Sort of new with the whole treeview control and I was wondering is there a way to build a treeview menu using my SQL data I pull from a database instead of having to hard code it into the webpage. Pasted below is my current code but what I would like to do is instead make a table in SQL that data can be added or modified to and have it generate the menu according to the data instead. Thanks for the help, Mike <mytree:treeview...
13
2531
by: mowsen | last post by:
Hello Group, i'm using a little "ajax" loader script to dynamically load files into different "div" tags on my main site. the code for this part looks like: function loader() { var args = loader.arguments; switch (args) { case
17
2730
by: NeoAlchemy | last post by:
I am starting to find more web pages that are using a query parameters after the JavaScript file. Example can be found at www.opensourcefood.com. Within the source you'll see: <script src="/shared/scripts/common.js?revision=1.6" type="text/javascript">. I am trying to see if there is any big deal to this or a best practice that is starting to creep up in the JavaScript community. If this is used only as a way to distinguish what...
0
8903
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
9277
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9130
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8129
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6716
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3236
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2663
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2161
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.