Problem creating a GetNextKey Stored Procedure

I am trying to create a GetNextKey stored procedure given
a tablename passed a parameter, but have had problems creating it.

Here is what I have so far:
CREATE PROCEDURE GetNextKey @Tablename Varchar(10)

Declare @sSQLStatement Varchar(255)
        @CurKey Integer,
        @NextKey Integer
select @sSQLStatement = 'select @CurKey=max(prkey) from ' +@Tablename
Exec (@sSQLStatement)

select @NextKey = @CurKey + 1

At first I had a problem using a variable in the from
clause of an SQL statement. I got around this by building
the sql statement in a variable and executing it using
"Exec". When I did this and executed the stored proc. however, I got the following error: "Must declare variable

I am trying to find out the following:

a) If I can use a variable in the from clause of an SQL
b) How I can assign the value to @CurKey in the SQL     statement executed with "Exec".
c) How I can return @NextKey as the result of the stored
d) If someone has created a similar procedure and can show
    me ways to do it.

Thanks in advance to anyone who reads/responds to my question.
Who is Participating?
Jim_SQLConnect With a Mentor Commented:
Hi, here is some sample code that answers your questions (I hope).

---------------- CREATE & POPULATE A TABLE ----------------
drop table TestFreddy
create table TestFreddy (prkey int identity(2,3), SomeThing varchar(15))
set nocount on
Declare @i int
select @i=1
WHILE @i<200
      insert into TestFreddy VALUES("Something"+convert(varchar, @i))
      select @i=@i+1
select * from TestFreddy
---------------- SUGGESTION FOR YOUR CODE----------------
drop proc GetNextKey
CREATE PROCEDURE GetNextKey @Tablename Varchar(30), @PrmyKeyFieldName varchar(30)
set nocount on
Declare @NextKey Integer

create table #ReturnValue(R int)

insert into #ReturnValue
EXECUTE("select max(" + @PrmyKeyFieldName + ") from " + @Tablename)

select @NextKey = R+1 from #ReturnValue

return (@NextKey)
---------------- TEST THE RESULT ----------------
Declare @NextKey int
EXEC @NextKey = GetNextKey "TestFreddy", 'prkey'
select @NextKey '@NextKey'
Using the Identity property might be of use for what you are doing (I don't know), but if it is then take a look at the code I used to populate the table... you can set the start & step values (2,3) to (1,1) if that would be better for you.

(Hope this helped.)
cseanAuthor Commented:
Edited text of question
cseanAuthor Commented:
Adjusted points to 200
cseanAuthor Commented:
Thanks Jim_SQL,

The bottom section of your response was very helpful. I never looked into temporary tables enough. That was the key to solving this problem.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.