Board index » delphi » Help: Need help creating a GetNextKey Stored Procedure

Help: Need help creating a GetNextKey Stored Procedure

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

 

Re:Help: Need help creating a GetNextKey Stored Procedure


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

Quote
Sean Chard wrote in message <34CF5378.5...@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: 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

Re:Help: Need help creating a GetNextKey Stored Procedure


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

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

I know of no easier way.  The table name cannot be a parameter.

Quote
> b) If there is a way of assigning the value to the @CurKey variable
>     in the SQL statement executed with "Exec"

Yes, you must declare @CurKey within the EXEC statement like this:
EXEC('DECLARE @CurKey Integer
SELECT @CurKey = MAX(prkey) FROM ' + @Tablename)

Quote
> c) How I can return the variable @NextKey as the result of the stored
>     procedure back to my Delphi app.

Like this:
EXEC('DECLARE @CurKey Integer
SELECT @CurKey = MAX(prkey) FROM ' + @Tablename
SELECT @CurKey + 1)

Quote
> d) If someone out there as written a similar stored proc. and can
> show     me ways to do it.

I think I just did.

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

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

Re:Help: Need help creating a GetNextKey Stored Procedure


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

Re:Help: Need help creating a GetNextKey Stored Procedure


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"

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

>Sean Chard wrote in message <34CF5378.5...@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: 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

Re:Help: Need help creating a GetNextKey Stored Procedure


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

Quote
>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 denOu...@compuserve.com
Wezep - Netherlands

Other Threads