I have a table to get the nextID (I can't use the id column property on a couple tables since data already exists). The SQL Server table is called SCHID with a layout of:

RECORDID I <Indenty Column>
TableName  VarChar (5)
LastID   I

I'd like to be able to create a stored procedure on the SQL server that I can pass it the table name and get the next id, if there is no record in for that table insert a record and return 1 as the lastid

Here is what I have that kinda starts the process:

CREATE procedure sp_Next_id 
@tablename varchar,
@nreturn int output
as
declare @nLastID int
select nlastid=lastid from schid 
	where tablename=@tablename

if @@rowcount = 0
   set @nreturn=1
   set @nlastid=1
   insert into schid (tablename,lastid) values (@tablename,@nlastid)
   GO

I Call it with:

lccmd=[exec sp_next_id "CT_PAT",?@nNextID]
? SQLEXEC(gnConnHandle,lcCmd)

This starts to work, but always only stores the 1st character of the table name to the schid table, thus if I run the same command again, it doesn't find a match.

Here is what I wanted, but it doesn't pass the syntx check in when building the stored procedure in SQL

CREATE procedure sp_Next_id 
@tablename varchar,
@nreturn int output
as
declare @nLastID int
select nlastid=lastid from schid 
	where tablename=@tablename

if @@rowcount = 0
   set @nreturn=1
   set @nlastid=1
   insert into schid (tablename,lastid) values (@tablename,@nlastid)
   GO
else
   set @nReturn=@nlastID+1
   update schid set lastID=@nLastID

Any Help Appreciated