🚀 go-pugleaf

Sean,

The way to do this is as follows.  i have made it possible for you to change
the Key Field name :

create table test2 (keyField int)
go
insert into test2 values (10)
go

CREATE PROCEDURE GetNextKey @Tablename Varchar(30),@KeyField Varchar(30)
AS
Declare @NextKey Integer
create table #Curkey (CurKey int)
insert into #Curkey Exec ('select max('+@keyField+') from '+@Tablename)
select Curkey + 1 from #Curkey
go

exec GetNextKey "test2","KeyField"

I hope this was of use to you.

Steve Robinson
steve@Samurai1.demon.co.uk


Sean Chard wrote in message <34CF5378.5F69@recruiter.ca>...
>Hello.
>
>I am trying to create a stored procedure to get the next available
>key from a specified table, so I can use it in my Delphi app.
>
>Here is the stored proc. as I have it so far:
>
>-----------------------------------------------------
>CREATE PROCEDURE GetNextKey @Tablename Varchar(10)
>AS
>
>Declare @sSQLStatement Varchar(255),
> @CurKey Integer,
> @NextKey Integer
>
>select @sSQLStatement = 'select @CurKey=max(prkey) from '+@Tablename
>Exec (@sSQLStatement)
>
>select  @NextKey = @Curkey + 1
>-------------------------------------------
>
>The first problem I encountered was specifying the tablename in the
>from clause of the SQL statement. I got around this by building a string
>containing the SQL statement and executing it with "Exec". However, when
>I do this, I get a "Must declare variable '@CurKey'" error.
>
>I am trying to find out the following:
>a) If there is an easier way to use a variable in a from clause of an
>    SQL statement
>b) If there is a way of assigning the value to the @CurKey variable
>    in the SQL statement executed with "Exec"
>c) How I can return the variable @NextKey as the result of the stored
>    procedure back to my Delphi app.
>d) If someone out there as written a similar stored proc. and can
>show     me ways to do it.
>
>Reply via email to: sean@recruiter.ca
>
>Thanks in advance to anyone who replies.
>Sean Chard
>
>
>
>
>--
>---------------------
>Sean S. Chard
>Programmer / Analyst - The Recruiter Inc.
>Home: (416) 244-3100   Work: (905) 773-0570
>Email: sean@recruiter.on.ca    WWW:
>http://www.recruiter.on.ca/people/sean
>