🚀 go-pugleaf

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