"Russell Mangel" <russell@tymer.net> wrote in message
news:emE8QhkEFHA.3780@TK2MSFTNGP09.phx.gbl...
> I am working with an old legacy SQL 7.0 database (it has poor design), and
I
> have to create a SP which does the following:
>
> Determine the largest value of an 'int' column for a table.
>
> Here is a working solution, but it will be very long, since I need to
> support 97 tables, in my legacy database.
>
> Is there an shorter/simpler way to achive my goal?
>
> -- NorthWind database
> CREATE PROCEDURE Utils_GetMaxID
> @tableName varchar(32), -- a table name in Northwind
> @maxID int OUTPUT -- return the largest PrimaryKey 'int' value
> AS
>
> IF @tableName = 'Employees'
> SELECT @maxID = MAX(EmployeeID) FROM Employees
> ELSE IF @tableName = 'Orders'
> SELECT @maxID = MAX(OrderID) FROM Orders
> ELSE
> RAISERROR ('Table does not exist.', 16, 1)
>
>
> -- Maybe we could use a temp table and dynamic SQL to get maxID?
> -- I wish I could use, FROM @tableName (but SQL does not allow it)
>
>
>
>
>

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1108393095.064840.174150@f14g2000cwb.googlegroups.com...
> Why not just get rid of this poorly defined and coded module? It has
> no cohesion at all -- what would you properly name it? The "find
> _max_squid_or_employee_or_bankaccount_or.. or_apples_id" procedure!
>
> Passing table names is absurd as well as a violation of the basic
> prinicples of software engineering. Since each table is a totally
> different kind of thing, their identifiers will be totally different.
> You can get a SSN, a phone number or an ISBN back from this disaster.
>
> But if you are determiened to keep doing it wrong, use dynamic SQL and
> expect to get SQL injections, loss of integrity and loss of security.
>
> Then find the guy that wrote this and kill him to prevent future
> problems :) Then completely re-design this schema. The number of
> tables sounds too high for a medium sized business and if he writes
> code like this, he probably screwed up everything from the start.
>