I still don't know about the first question, but I have found an answer to the second question... I did figure out how to use the sp_executesql command to return a value using OUTPUT parameters instead of creating a temporary table, (I'll post the code below).

We also have decided that individual ID tables for each data table was not any more efficient than one ID table containing the name of each data table and its next ID value. In an attempt to clarify why we needed to determine ID values this way, (I have a feeling Bob was not impressed with my first explanation), here goes:

In the case where you have a master-detail relationship between two distinct datasets that you are displaying on one page - If you want to allow the user to input the information for both the master and the detail, and post all at once, you need the ID of the parent before you can post the detail record. I know there are ways to do this under normal circumstances, but again, due to the third-party architecture we're using, we needed the master ID before posting the detail record.

Anyway, if anyone is interested, here's the stored procedure I ended up with:

CREATE PROCEDURE gasp_generate
        @table_name	varchar(50)
AS

SET NOCOUNT ON

declare
      @sqlcmd	nvarchar(255),
      @return_val	int

      SET @sqlcmd = 'BEGIN TRAN UPDATE gasg_generator' +
		' SET id_value = id_value + 1' + 
		' FROM gasg_generator WHERE table_name = ''' + @table_name +
		''' SELECT @return_val = id_value' +
		' FROM gasg_generator WHERE table_name = ''' + @table_name + ''' COMMIT'

      EXEC sp_executesql @sqlcmd, N'@return_val int output', @return_val output

      RETURN @return_val
GO

>Hello!
>
>We have been developing a standard by which all of our future web-based applications will interface with our SQL Server databases (SQL Server 7 now, we're in the process of migrating to 2000). One problem we have been dealing with is the concept of record id's, (identity columns, etc.) as they pertain to web-based applications, (ie we want the id value without having to refresh the web page, so identity columns will not work.)
>
>I am familiar with using a generator table, which would contain the "next id" value for each table in the database; Create a stored procedure to lock this table, grab the value, then increment the value for the specified table and unlock it.
>
>Due to the number of tables & users we may potentially be dealing with, we have discussed the possibility of creating a generator table for each table in the database, (to reduce the load on the one generator table). Then, any time an insert is required for any table, a stored procedure would be called to retrieve the next id for that table from its generator table.
>
>First Question: Is this necessary? Is the overhead of that many additional tables worth the payoff of reducing the load to a primary generator table? (Say there were a couple hundred users, and as many as 100 tables)
>
>If it IS a good idea to create a generator table for each database table, we would like to create ONE stored procedure with a TABLE_NAME parameter, so that we don't have to create a stored procedure for each generator table. I have been able to create such a stored procedure, but in order to pass in the generator table name to used, I have to create a SQL command string, and issue the following statement: sp_executesql @sql_command_string
>
>This works great to increment the value, but to actually return a value, I have to create a temporary table to store the value in, and then select the value from the temp table, drop the table, then return the value. I have not figured out a way to return a value from the sp_executesql command. (see stored procedure code below)
>
>Second Question: Given the above setup, is there any other way to return a value from a stored procedure when the value is selected from a table, if the name of that table was a parameter to the stored procedure?
>If not, is the overhead of creating and dropping a temporary table each time this stored procedure is called, more taxing than just having one generator table in the first place?
>
>
>/************************************************/
>CREATE PROCEDURE gasp_gen_id
> @table_name varchar(50)
>AS
>
>SET NOCOUNT ON
>
>declare
> @sqlcmd nvarchar(255),
> @return_val int
>
> SET @sqlcmd = 'UPDATE gen_table' +
> ' SET id_val = id_val + 1' +
> ' FROM ' + @table_name + ' gen_table (holdlock)'
> EXEC sp_executesql @sqlcmd
>
>create table #temp_tbl
> (temp_val int)
>
> SET @sqlcmd = 'INSERT INTO #temp_tbl (temp_val) ' +
> 'SELECT id_val' +
> ' FROM ' + @table_name
>
> EXEC sp_executesql @sqlcmd
>
>SELECT @return_val = temp_val FROM #temp_tbl
>
>DROP TABLE #temp_tbl
>
>RETURN(@return_val)
>/************************************************/
>
>Thanks in advance!