Writing a query ...

[bcap]

Hi,

I am looking to write a piece of code better.

Lets say I have a table called CustInfo with CustID, FirstName,
LastName, and Subscription Level for simplicity.

How can I create a query that I would be to:

1) Get all records no matter what subscription level they are in
2) Choose only records that have a selected status types
3) Declare that there are no records

Currently, I am using two queries (one that just selects all, another
that needs a parameter) and making a decision based on if there is a
status type. I'd like to learn a more efficent way to do this. Any
advise would be appreciated!

 

[Evertjan.]

bcap wrote on 25 jul 2009 in microsoft.public.inetserver.asp.general:

I am looking to write a piece of code better.

Better than what?

Show us your code, only the essence please.

Lets say I have a table called CustInfo with CustID, FirstName,
LastName, and Subscription Level for simplicity.

No, let us hear what engine you are using.

How can I create a query that I would be to:

1) Get all records no matter what subscription level they are in
2) Choose only records that have a selected status types
3) Declare that there are no records

Is this a school assignment?
Did you read some of the many SQL tutorials?

Currently, I am using two queries (one that just selects all, another
that needs a parameter) and making a decision based on if there is a
status type.

What is a "status type"?

I'd like to learn a more efficent way to do this. Any
advise would be appreciated!

Learning is not letting someone else do the task!

 

[bcap]

Thank you for your reply Evertjan!

I am using SQL Server with ASP Classic. This is not a school project
(though I want to take some classes), I am a IT Project Manager trying
to learn code and modify existing projects. Here is what the query
currently looks like, it gets the job done. I want to learn and
understand if this process can be done better.


if request.querystring("StatusDisplay")="" then

strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo"

Else

strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo WHERE
SubLevel=" & request.querystring("sublevel")

End If

 

[Bob Barrows]

Thank you for your reply Evertjan!

I am using SQL Server with ASP Classic. This is not a school project
(though I want to take some classes), I am a IT Project Manager trying
to learn code and modify existing projects. Here is what the query
currently looks like, it gets the job done. I want to learn and
understand if this process can be done better.


if request.querystring("StatusDisplay")="" then

strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo"

Else

strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo WHERE
SubLevel=" & request.querystring("sublevel")

End If

Aside from using dynamic sql, there is nothing wrong with this. Other ways
of doing it will decrease the amount of code, but will lead to less
efficient queries. There are some good articles here:
http://www.sommarskog.se/ - read the one about dynamic search conditions
(and then read the one about dynamic sql)

Here is my canned reply about dynamic sql:

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See these links for a better, more secure way to execute your queries by
using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Using Command object to parameterize CommandText:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
Select statement:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd


Personally, I prefer using stored procedures,
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&

 

[Evertjan.]

Bob Barrows wrote on 25 jul 2009 in
microsoft.public.inetserver.asp.general:

A bit strange, anwering me by name and not supplying one yourself,
"bcap" not sounding like a usable [nick]name.

Aside from using dynamic sql, there is nothing wrong with this. Other
ways of doing it will decrease the amount of code, but will lead to
less efficient queries. There are some good articles here:
http://www.sommarskog.se/ - read the one about dynamic search
conditions (and then read the one about dynamic sql)

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:

I am not that afraid of sql-injection as long as one is constantly on
guard.

If your sublevel is an integer, this:

strSql = "SELECT CustID, FName, LName, SubLevel FROM CustInfo "_
"WHERE SubLevel = " & CInt(request.querystring("sublevel"))

will prevent sql-injection adequately.

When you have a series of levels, you could try:

"WHERE SubLevel > " & CInt(request.querystring("sublevel"))


I agree with Bob that the danger is imminent when you become sloppy.

===================

Always first visualize your SQL result:

<%
strSql = "SELECT ....."
response.write "<p> & strSql & "<p>":response.end
' set mD = conn.Execute(strSql)
%>

and only execute it when you are completely satisfied:

<%
strSql = "SELECT ....."
' response.write "<p> & strSql & "<p>":response.end
set mD = conn.Execute(strSql)
%>

and even then keep a copy of your database
for back-uping to a previous status quo.

 

[bcap]

Thank you all for your time and thoughts, very much appreciated!

 

[Evertjan.]

bcap wrote on 25 jul 2009 in microsoft.public.inetserver.asp.general:

Thank you all for your time and thoughts, very much appreciated!

Please always quote on usenet.

This is not an idle request,
as it is pefectly unclear what you are replying on and to whom.

Not all news servers fill their items ina perfectr and timely fassion, and
some are deleting them more quickly than others.

 

[Dooza]

Thank you for your reply Evertjan!

I am using SQL Server with ASP Classic. This is not a school project
(though I want to take some classes), I am a IT Project Manager trying
to learn code and modify existing projects. Here is what the query
currently looks like, it gets the job done. I want to learn and
understand if this process can be done better.


if request.querystring("StatusDisplay")="" then

strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo"

Else

strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo WHERE
SubLevel=" & request.querystring("sublevel")

End If

SELECT CustID, FName, LName, SubLevel
FROM CustInfo
WHERE (@SubLevel IS NULL OR SubLevel = @SubLevel)

@SubLevel is your input variable, if it is NULL, then it lists
everything, if it isn't NULL, it tries matches it.

I don't know how you would put this in Dynamic SQL, as I would use a
Stored Procedure to do this normally.

Dooza