Solved

Next ID Stored Procedure MSSQL ERROR

Posted on 2013-11-18
4
611 Views
Last Modified: 2013-11-19
Im trying to create stored procedure. Where i'll pass [TABLE NAME] in return i need max ID of the table so i can insert record with that table with new ID.


USE [ASSETMANAGEMENT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].NextId 
@TableName varchar(50)

AS

DECLARE @NextId Int

set @NextId = (SELECT max(UID)+1 FROM  @TableName)

if @NextId = NULL begin
      set @NextID = 1
      RETURN 1
end 

Open in new window


ERROR:
Msg 1087, Level 15, State 2, Procedure NextId, Line 9
Must declare the table variable "@TableName".
Msg 137, Level 15, State 2, Procedure NextId, Line 11
Must declare the scalar variable "@NextId".
Msg 137, Level 15, State 1, Procedure NextId, Line 12
Must declare the scalar variable "@NextID".
0
Comment
Question by:xeondxb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 166 total points
ID: 39656064
USE [ASSETMANAGEMENT]
GO

[code]SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].NextId 
@TableName varchar(50)

AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @NextId Int

	SET @NextId = (SELECT max(UID)+1 FROM  @TableName)

	SELECT ISNULL(@NextId, 0)
END
GO

Open in new window

0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 167 total points
ID: 39656086
Hi,

You cant use a variable directly in a query for tablename but use exec statement.

CREATE PROCEDURE [dbo].NextId 
@TableName varchar(50)

AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @sql NVARCHAR(max)
	SET @sql = 'SELECT max(ID)+1 FROM  ' + @TableName
	
	exec sp_executesql @sql
END
GO

Open in new window


A better way is to use primary key, and set identity(1,1) auto increment, which will automatically increment 1, instead of you doing this, so that even multiple users can insert records, and you wont have data integrity problems.
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 167 total points
ID: 39656423
(no points... just a small comment)


A better way is to use primary key, and set identity(1,1) auto increment, which will automatically increment 1, instead of you doing this, so that even multiple users can insert records, and you wont have data integrity problems.

Very good point.  On a busy system, two threads could easily end up w/the exact same ID using your current logic. If having a unique ID is important to your process, better to let the db handle incrementing.

EDIT: While neither valli_an or I recommend this option, technically the code needs a small tweak. The result column needs an alias - or you won't be able to access the value in CF (not easily):

          SET @sql = 'SELECT max(ID)+1 AS TheResult FROM  ' + @TableName
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39659665
One other note for the archives.  In addition to thread safety issues, using dynamic SQL poses a SQL injection risk. So always validate object names against system metadata (INFORMATION_SCHEMA) before using them in SQL, otherwise you put the db at risk.
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question