Removing embedded SQL from database application

I inherited an existing database application about 6 months ago. I've
finished converting it to use SQL Server 2000 as the backend (MS Access
2002 frontend w/ Userforms+VBA code pointing to the backend).

In its current state the application makes use of a ton of embedded SQL
statements and I'm thinking about the best way to clean this up. It
seems like the best way would probably to encapsulate each of the SQL
statements into its own stored proc and then have the MS Access
application call the sp. However, this would result in a very large
number of stored procedures.

Anyone have any links or suggestions regarding best practices on
removing embedded SQL scattered throughout a DB app? Thanks.Beowulf (beowulf_is_not_here@.hotmail.com) writes:
> I inherited an existing database application about 6 months ago. I've
> finished converting it to use SQL Server 2000 as the backend (MS Access
> 2002 frontend w/ Userforms+VBA code pointing to the backend).
> In its current state the application makes use of a ton of embedded SQL
> statements and I'm thinking about the best way to clean this up. It
> seems like the best way would probably to encapsulate each of the SQL
> statements into its own stored proc and then have the MS Access
> application call the sp. However, this would result in a very large
> number of stored procedures.
> Anyone have any links or suggestions regarding best practices on
> removing embedded SQL scattered throughout a DB app? Thanks.

Certainly sounds like a daunting task. An alternative is review all
embedded SQL and make sure that no SQL statements interpolate values,
but all queries are parameterised. Furthermore, make sure that tables
are prefixed with dbo.

As for why, read these two sections:
http://www.sommarskog.se/dynamic_sql.html#SQL_injection
http://www.sommarskog.se/dynamic_sql.html#queryplans

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

removing embedded duplicate rows

I have a table that contains a field with data like:
dog 1
dog 2
cat 1
cat 2
I only what to return one of the values. DISTINCT doesn't do it. Any
suggestions?
Thanks,
BobWhich value do you want? Distinct rolls up multiple values that are the
same. Your four values are all different, and thus cannot be rolled up.
Describe what you expect to see from the sample data you quoted.
More info, please.|||Assuming table below with 2 cols name & id
name id
-- --
dog 1
dog 2
cat 1
cat 2
-- following will get u the 1st of every grp with same name
select name, id
from t
where id in (select min(id) from t group by name)
Rakesh
"bday55" wrote:

> I have a table that contains a field with data like:
> dog 1
> dog 2
> cat 1
> cat 2
> I only what to return one of the values. DISTINCT doesn't do it. Any
> suggestions?
> Thanks,
> Bob
>|||On Wed, 10 Aug 2005 22:51:01 -0700, Rakesh wrote:

>Assuming table below with 2 cols name & id
>name id
>-- --
>dog 1
>dog 2
>cat 1
>cat 2
>-- following will get u the 1st of every grp with same name
>select name, id
>from t
>where id in (select min(id) from t group by name)
Hi Rakesh,
But that won't work if the rows are changed to

>name id
>-- --
>dog 1
>dog 2
>cat 2
>cat 3
Here's a better one:
SELECT name, id
FROM t AS t1
WHERE id = (SELECT MIN(id) FROM t AS t2 WHERE t1.name = t2.name)
Or, even simpler:
SELECT name, MIN(id)
FROM t
GROUP BY t
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)