ASP search screen and MS-SQL 2k

Hello,

I am looking for help with the following situation.

I have created a simple call tracking system that is integrated
with our accounting / warehousing program in the company. The call
tracking system uses ASP 3.0 and MSSQL 2000 sitting on an Intranet web
server. All appears to be working well with the software. I am
trying to create a search screen to search through the calls based on
different criteria.

I have an ASP script that generates a SELECT statement based on
the selected criteria but this is sent off to the SQL server as the
statement. I want to use a stored procedure to speed up the process.
Is it possible to create a stored procedure for this search even when
the WHERE clause and criteria keeps changing?

TIALook into Full Text Indexing on your SQL Server tables. There is a Sql
Service dedicated to this. This article touches on the capability...

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8967

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"rdlebreton" <rdlebreton@.hotmail.com> wrote in message
news:80376060.0312111117.4f999bf2@.posting.google.c om...
> Hello,
> I am looking for help with the following situation.
> I have created a simple call tracking system that is integrated
> with our accounting / warehousing program in the company. The call
> tracking system uses ASP 3.0 and MSSQL 2000 sitting on an Intranet web
> server. All appears to be working well with the software. I am
> trying to create a search screen to search through the calls based on
> different criteria.
> I have an ASP script that generates a SELECT statement based on
> the selected criteria but this is sent off to the SQL server as the
> statement. I want to use a stored procedure to speed up the process.
> Is it possible to create a stored procedure for this search even when
> the WHERE clause and criteria keeps changing?
> TIA|||[posted and mailed, please reply in news]

rdlebreton (rdlebreton@.hotmail.com) writes:
> I am looking for help with the following situation.
> I have created a simple call tracking system that is integrated
> with our accounting / warehousing program in the company. The call
> tracking system uses ASP 3.0 and MSSQL 2000 sitting on an Intranet web
> server. All appears to be working well with the software. I am
> trying to create a search screen to search through the calls based on
> different criteria.
> I have an ASP script that generates a SELECT statement based on
> the selected criteria but this is sent off to the SQL server as the
> statement. I want to use a stored procedure to speed up the process.
> Is it possible to create a stored procedure for this search even when
> the WHERE clause and criteria keeps changing?

Yes, although the way to gain speed is more likely to review indexing,
or constrain what users can search on.

Anyway, I have an article on the topic on my web site. It's a bit
long, but it covers the topic fairly extensively. The link is
http://www.algonet.se/~sommar/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns944F3B58AE2FYazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
<snip!
Holy S--!

You've got a lot on this web site!

It's going to take me the whole weekend to digest it all.

Thanks

Arrays in SQL database

Hi;
Can MS-SQL support Arrays? I need a float that occurs 72 times in a
table and I don't want to have 72 seperate fields but can't see a way of
creating an array?
Thanks in advance.
David
*** Sent via Developersdex http://www.examnotes.net ***Hi,
There is no array concept in SQL Server. But you could use Dynamic SQL's to
provide an equalent.
Look into the great article:-
http://www.sommarskog.se/arrays-in-sql.html
Thanks
Hari
SQL Server MVP
"DavidC" <Cervelli@.Adelphia.Net> wrote in message
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||No, SQL Server does not support arrays
Yo can use either dynamic sql or udf
CREATE PROCEDURE array_method_1
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC array_method_1
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
GO
"DavidC" <Cervelli@.Adelphia.Net> wrote in message
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You can share them by (global) temporary tables, just follow the link Hari
presented.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DavidC" <Cervelli@.Adelphia.Net> schrieb im Newsbeitrag
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
> I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
Having just spent 20+ hours days undoing an "Array" column (i.e., column
containing a string with comma separated values), I can assure you that
having 72 columns is much better than having a CSV-string.
An "array" is a terrible hack and a huge design flaw created by a lazy
programmer.
Yeah, it's more work. Yeah, it's not fun. But, this is why they pay you the
big bucks.
Just do it.
Perhaps if you share the problem domain we may be able to help with a
cleaner design for this (aparantly) complex data.
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis|||>> Can MS-SQL support Arrays?
No
Perhaps you can make your overall requirement clearer. Most likely your
logical design is flawed which forces you to come up with such complex table
structures. Are you sure these are truly 72 distinct attributes of the
entity type you are modeling? Or does the collective 72 repetitions form a
single attribute from a conceptual POV?
Anith|||Even if the number of float values will be constant at 72 per record, it
seems the logical place to store them in a relational database would be a
foreign key table. Or perhaps we are talking about a lookup table with 72
records. Loading the values into an array would be something you implement
on the presentation / GUI side. Am I understanding what it is you want to
do?
"DavidC" <Cervelli@.Adelphia.Net> wrote in message
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***