Problem with Variables. SQL Server 2000
-
I am writing a stored procedure that returns the next value .
[Code]
CREATE PROCEDURE sp_sGetNextApplicantID
@Country nvarchar(10),
@OutPutApplicID int output
AS
DECLARE @ApplicID as int
DECLARE @SelSQL nvarchar(1000)
DECLARE @UpdSQL as nvarchar(1000)
BEGIN TRAN GetNextApplicID
SET @SelSQL = 'SELECT @ApplicID = ' + @Country + 'NextApplicantID '
SET @SelSQL = @SelSQL + 'FROM NextNumbers'
EXEC(@SelSQL)
SET @UpdSQL = 'UPDATE NextNumbers '
SET @UpdSQL = @UpdSQL + 'SET ' + @Country + 'NextApplicantID = ' + CAST ((@ApplicID + 1) as nvarchar)
EXEC(@UpdSQL)
SELECT @OutPutApplicID = @ApplicID
COMMIT TRAN GetNextApplicID
GO
[/Code]
When the Exec(@UpdSQL) is run it keeps on returning the error @ApplicID needs to be declared, I though I had!
Please Help.
Kev.
-
The reason is that you're using dynamic SQL, which you really should avoid.
Even if you'd get it to work, this code won't behave as you might expect. There is a chance that two simultaneous requests may get the same number returned.
If I read this correct, your NextNumbers table looks like it has a separate column that is named [country]NextApplicantID, where [country] is different for each column...? <shiver> this is really bad, actually.
What you should have is a table with two columns, NextId and Country.
Then for each row you store the id counter, and to retrieve it, you just ask for the nextId where country = @country.
In order to eliminate risks of dealing out duplicate id's to concurrent users, you can use the method in the below procedure, though this requires that you have a counter table that supports it.
-- Script downloaded 4/20/2005 5:46:04 AM
-- From The SQL Server Worldwide User's Group (http://www.sswug.org)
-- Scripts provided AS-IS without warranty of any kind use at your own risk
if object_id('getNextID') is not null drop proc getNextID
go
create proc getNextID @tabname sysname, @nextid int OUTPUT
as
-- file: getNextID.sql
-- why: generic proc that returns the next avilable id-counter for the specified table
-- without the possibility for concurrent users to recieve the same number.
-- (the update method may not be supported by Microsoft, it does however work)
-- use at your own risk.
--
-- Usage:
-- declare @varForNewId int
-- exec getNextID 'tableName', @varForNewId OUTPUT
-- @varForNewId now contains the new id....
-- table: uniqueId xxUx - table to keep the counter in - 1 row for each counter and table
--
-- by: Kenneth Wilhelmsson
-- when: 2001-02-09 - first version
set nocount on
declare @err int,
@rc int
-- check that counter for this table exists
if not exists ( select * from uniqueId where tablename = @tabname ) goto errhandler
-- get the next id
update uniqueId
set @nextid = nextId = nextId + 1
where tablename = @tabname
select @err = @@error, @rc = @@rowcount
if (@err <> 0) goto errhandler
if (@rc <> 1) goto errhandler
return @err
errhandler:
declare @errmsg varchar(255)
set @errmsg = 'procedure: ' + object_name(@@procid) + ' *** FATAL ERROR *** '
raiserror(@errmsg, 16, -1) with log
return @err
go
/*
example tabdef:
create table uniqueId
( tablename sysname not null,
nextid int not null
)
alter table uniqueId add constraint PK_uniqueId_ucix primary key clustered (tablename)
*/
-- end script
/Kenneth
-
The statement in the EXEC is run in a separate batch, so it needs to be declared there.
However I think I would rather suggest that you change the design of the NextNumbers table. I assume it looks something like this now:
NextNumbers (USANextApplicantID, SwedenNextApplicantID ...)
And you have only one row in the table. That design is not the best, and it leads to these troubles with dynamic sql you are having. If you instead change it to:
NextNumbers (Country, NextApplicantID)
Then you can do something like the following:
SELECT @ApplicID = NextApplicantID FROM NextNumbers
WHERE Country = @Country
UPDATE NextNumbers SET NextApplicantID = ...
See how much easier it is with static SQL?
-
You need to use sp_executeSQL with an output parameter @ApplicID in order to reuse it after the dynamic stuff has finished. EXEC won't give you that functionality. So, something along these lines:
DECLARE @stmt nvarchar(4000)
DECLARE @rowcount bigint
DECLARE @table nvarchar(255)
SET @table = 'authors'
SELECT @stmt = 'SELECT @count = COUNT(*) FROM ' + @table
EXEC sp_executesql @stmt, N' @count bigint output', @rowcount OUTPUT
Now you can use @rowcount in your next statement. However, Kenneth's and Chris's point are very valid.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for that all. I am new SQL Server and my brain has been left at home. Got it working, usde Kenneths suggestion.
Thanks again.
Kev.
-
Personally, I do prefer (and use) the 'single-update' method to increment and return the next countervalue in one go. It makes it unnecessary to wrap in explicit transactions and such. It's physically impossible for the server to return the same counter to more than one caller.
update counterTable
set @nextid = nextId = nextId + 1
where counterQualifier = @counterQualifier
The above method both increments the counter and assigns the incremented value to @nextid at the same time.
However, if you want to do it the 'traditional' way for whatever reason, in order to protect the counter while 'in-process' (since it's two statements) you must do a few more things.
BEGIN TRAN
UPDATE counterTable
SELECT the new value
COMMIT
Note that the order is critical here - UPDATE must be before the SELECT, and the two must also be within an explicit transaction in order to ensure that noone else sneaks in between and manages to select the newly updated value.
/Kenneth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply