🚀 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
>

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"
<steve@samurai1.demon.co.uk> wrote:

>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
>>
>

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

>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
>-------------------------------------------

Did you consider using a generator? I usually use a generator in a
stored procedure to generate unique key values. It works very simple
and safe. Using the max function, may cause key violations when users
simultaneously insert records, generators don't have that problem.


E:mail denOuden@compuserve.com
Wezep - Netherlands