Next ID Stored Procedure MSSQL ERROR

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".
xeondxbAsked:
Who is Participating?
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
Louis01Commented:
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
 
_agx_Commented:
(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
 
_agx_Commented:
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
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.