Use of variable in identity function !

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use of variable in identity function !

Author

Topic

mageshks
Yak Posting Veteran

59 Posts

Posted - 2008-06-04 : 09:35:05

declare @number int

set @number = 100

SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(int, @number, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee

Is there any way i can use variable inside the identity function like in the above example?.

Is there any other alternative?

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-04 : 11:29:46

With Dynamic SQL

declare @number int, @sql varchar(8000)

set @number = 100

SELECT @sql='SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(int, '+cast(@number as varchar(10))+', 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee'
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 12:32:46

Mageshk, you don't need dynamic SQL at all for this.

declare @number int

set @number = 100

SELECT emp_id AS emp_num, 
fname AS first, 
minit AS middle, 
lname AS last, 
@number + IDENTITY(int, 0, 1) AS job_num, 
job_lvl AS job_level, 
pub_id, 
hire_date
INTO employees 
FROM employee

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-04 : 13:24:22

Thats good one Peso

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 13:28:50

Thank you Madhi.

E 12°55'05.25"
N 56°04'39.16"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-04 : 13:31:31

>>@number + IDENTITY(int, 0, 1) AS job_num,

did you guys run this? I get an "incorrect syntax..." error
It is probably me but I haven't figured it out yet.

this works for me however


create table #employees 
       (first varchar(10)
       ,middle varchar(10)
       ,last varchar(10)
       ,job_num int identity(1,1)
       )
EDIT:

declare @number int
set @number = 100
dbcc checkident(#employees, reseed, 100 @number)

insert #employees (first,middle, last)
SELECT top 10
       left(name, 10)
       ,left(name, 10)
       ,left(name, 10)
FROM sysobjects
order by id

select * from #employees
drop table #employees

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 13:57:22

Good spot. My suggestion doesn't work in sql server 2005 sp2.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources