• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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

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
'@CurKey'".

I am trying to find out the following:

a) If I can use a variable in the from clause of an SQL
     statement.
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
    procedure.
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.
0
csean
Asked:
csean
  • 3
1 Solution
 
cseanAuthor Commented:
Edited text of question
0
 
cseanAuthor Commented:
Adjusted points to 200
0
 
Jim_SQLCommented:
Hi, here is some sample code that answers your questions (I hope).

---------------- CREATE & POPULATE A TABLE ----------------
drop table TestFreddy
GO
create table TestFreddy (prkey int identity(2,3), SomeThing varchar(15))
GO
set nocount on
Declare @i int
select @i=1
WHILE @i<200
    BEGIN
      insert into TestFreddy VALUES("Something"+convert(varchar, @i))
      select @i=@i+1
    END
GO
select * from TestFreddy
GO
---------------- SUGGESTION FOR YOUR CODE----------------
drop proc GetNextKey
GO
CREATE PROCEDURE GetNextKey @Tablename Varchar(30), @PrmyKeyFieldName varchar(30)
AS
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)
GO
---------------- TEST THE RESULT ----------------
Declare @NextKey int
EXEC @NextKey = GetNextKey "TestFreddy", 'prkey'
select @NextKey '@NextKey'
GO
/*
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.)
Jim
*/
0
 
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.

Sean
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now