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!