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: s...@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: s...@recruiter.on.ca    WWW:
http://www.recruiter.on.ca/people/sean

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
st...@Samurai1.demon.co.uk

Sean Chard <s...@recruiter.ca> wrote in article
<34CF5378.5...@recruiter.ca>...

Hope this helps,
William
--
William Tucker
Compu-Share, Inc.

WTuc...@Compu-Share.com
William.Tuc...@worldnet.att.net

The answer to c should have been:
EXEC('DECLARE @CurKey Integer
SELECT @CurKey = MAX(prkey) FROM ' + @Tablename + '
SELECT @CurKey + 1')

William
--
William Tucker
Compu-Share, Inc.

WTuc...@Compu-Share.com
William.Tuc...@worldnet.att.net

Here is what we came up with.  This creates the stored proc to get the
next key as an output param and creates the tables them selves all you
have to do is fill the table with vales

if object_id('dbo.tblNextKeyTable') is not null
begin
    drop table tblNextKeyTable
end
go

create table tblNextKeyTable
(
    TableName      varchar(32)    not null,
    KeyName        varchar(32)    not null,
    KeyValue       int            not null
)
go
grant all on tblNextKeyTable to public
go

if object_id('dbo.sp_GetNextKey') is not null
begin
    drop procedure sp_GetNextKey
end
go

create procedure sp_GetNextKey
@TableName      varchar(32)      = null,
@KeyName        varchar(32)      = null,
@Increment      int              = 1
as
declare @KeyValue int,
        @rowcount int

select @rowcount = count(*)
from   tblNextKeyTable
where  TableName = @TableName
and    KeyName   = @KeyName

if (@rowcount = 0)
begin
    select -1
    return
end

begin tran
save tran sp_GetNextKey_t1

update tblNextKeyTable
set    KeyValue  = KeyValue + @Increment
where  TableName = @TableName
and    KeyName   = @KeyName

if (@@error != 0) goto error_happen

select @KeyValue = KeyValue
from   tblNextKeyTable
where  TableName = @TableName
and    KeyName   = @KeyName

if (@@error != 0) goto error_happen

commit tran

select @KeyValue

return

error_happen:
rollback tran sp_GetNextKey_t1
raiserror 60000 "Error in sp_GetNextKey"
return
go

grant all on sp_GetNextKey to public
go

insert into tblNextKeyTable values ('customer_sites','site_id',10000)
go

if object_id('dbo.sp_GetNextKey2') is not null
begin
    drop procedure sp_GetNextKey2
end
go

create procedure sp_GetNextKey2
@TableName      varchar(32)      = null,
@KeyName        varchar(32)      = null,
@Increment      int              = 1,
@KeyValue       int              = null out
as
declare @rowcount int

select @rowcount = count(*)
from   tblNextKeyTable
where  TableName = @TableName
and    KeyName   = @KeyName

if (@rowcount = 0)
begin
    select @KeyValue = -1
    return
end

On Wed, 28 Jan 1998 17:26:03 -0000, "Steve Robinson"

On Wed, 28 Jan 1998 10:49:12 -0500, Sean Chard <s...@recruiter.ca>
wrote:

E:mail denOu...@compuserve.com
Wezep - Netherlands