[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Next ID Stored Procedure MSSQL ERROR

Posted on 2013-11-18
4
Medium Priority
?
621 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 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

649 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