>I definitely don't have 588. My problem is that all stored procedures will have exactly the same code with the exception of the table name. If I make one change, I will have to do it 588 (or so) times. How do you deal with this?
Unhappily. If they have to change, they have to change. We try to script simple changes but sometimes you just have to do it.
>Of course, there will be a major advantage in performance, I can appreciate. How about the WHERE clause though? What if the WHERE clause could be anywhere from a single line to 50 or 100 lines? If that is variable also, it defeats the purpose of having a sp for each table. Is that correct?
I'm not sure that I completely understand your question. Does "50 to 100 lines" translate to search arguments? If so, then sometimes you can work around things like that. Take the following proc:
CREATE PROCEDURE dbo.usp_getProductsByCategory
@categoryID int,
@showDiscontinuedProducts nchar(1) = 'F'
AS
IF @CategoryID IS NULL
BEGIN
RAISERROR('Bad parameters supplied to usp_getProductsByCategory', 16, 1)
RETURN 1
END
SELECT
[ProductID],
[ProductName],
[SupplierID],
[UnitPrice],
[UnitsInStock],
[Discontinued]
FROM
[Products]
WHERE
CategoryID = @categoryID
AND (discontinued = 0 OR @showDiscontinuedProducts = 'T')
IF @@ERROR != 0 RETURN 1
RETURN 0
It's used to retrieve all the products in a given category from the Northwind..products table. Of the two parameters only @categoryID is required. The other parameter allows the caller to filter the products by those that are discontinued or not.
-Mike