Solved

Next ID Stored Procedure MSSQL ERROR

Posted on 2013-11-18
4
600 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 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Detach & Attach 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 I will describe the Backup & Restore 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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now