?
Solved

Next ID Stored Procedure MSSQL ERROR

Posted on 2013-11-18
4
Medium Priority
?
629 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
  • 2
4 Comments
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 664 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 668 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 668 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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 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