Identity function

Question

Post reply

Identity function

PutJatDa

SSC-Addicted

Points: 448
More actions
April 20, 2005 at 3:45 am

#164048

Hi
Using the identity function as follows :-
IDENTITY(bigint, 1, 1) AS <field name>

However, rather than using 1 as the starting point, my starting point is derived and stored in a variable. However when I use the variable in the above statement, I get a syntax error.
IDENTITY(bigint, @StartPoint, 1) AS <field name>
Incorrect syntax near '@StartPoint'.

Can anyone help please? Can you not use a variable in the statement?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

It seems when looking in BOL that variables isn't supported.

/Kenneth

PutJatDa SSC-Addicted Points: 448 More actions · Answer 2

Thanks Kenneth. That's what I had found but was hoping someone had discovered an undocumented workaround.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Why do you need it to be dynamic? What's the reason for using the function this way? There may be some other way to solve the underlying actual problem?

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Not only this, but AFAIK you can use the IDENTITY function only in conjunction with SELECT INTO. And SELECT INTO used in a proc will cause a recompile which might cause locking and blocking etc...

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65142 More actions · Answer 5

A possibility (if you have permission to run DBCC

DECLARE @StartPoint bigint

SET @StartPoint = 1001

CREATE TABLE #Table (rowid bigint IDENTITY(1,1),field1 varchar(10))

DBCC CHECKIDENT('#Table',RESEED,@StartPoint)

INSERT INTO #Table (field1) VALUES ('Line 1')

INSERT INTO #Table (field1) VALUES ('Line 2')

INSERT INTO #Table (field1) VALUES ('Line 3')

SELECT * FROM #Table

DROP TABLE #Table

result

rowid field1

-------------------- ----------

1001 Line 1

1002 Line 2

1003 Line 3

Far away is close at hand in the images of elsewhere.
Anon.