use db name dynamically

• tharika1

  Newbie

  Points: 7

  More actions

  September 9, 2008 at 2:09 am

  #192631

  i want to get a value from another database table. the db name should pass as a variable. i try with the following code. please give a help to that

  declare @id as int

  declare @db as nvarchar(50)

  set @db='Employee'

  set @id=(exec ('use' + @db +'select max(empId) as lastEmloyee from Employee) ')

  set @id=@id+1

  select @id

• Renuka__

  Hall of Fame

  Points: 3656

  More actions

  September 9, 2008 at 2:23 am

  #868889

  Hi,

  Check if this is of some help:

  DECLARE @test_str varchar(255)

  DECLARE @dbname varchar(50)

  SET @test_str = 'use ' + '['+@dbname+']' +

  ' 'select max(empId) as lastEmloyee from Employee'

  EXEC (@test_str)

  [font="Verdana"]Renuka__[/font]

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 9, 2008 at 4:23 am

  #868928

  add following code in ur script

  declare @db as nvarchar(50)

  set @db='Employee'

  declare @lstr varchar(100)

  set @lstr ='select max(empId) as lastEmloyee from ' + @db + ' ..Employee'

  exec (@lstr)

  hope this will work 🙂

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• amit-470969

  SSC-Addicted

  Points: 437

  More actions

  September 9, 2008 at 8:17 am

  #869072

  HI,

  I THINK YOU WANT TO GENERATE EMPLOYEE ID AND ITS DATA TYPE IS INT.

  TRY THIS ...

  DECLARE @DBName Varchar(50)

  DECLARE @MaxId Varchar(500)

  DECLARE @ID int

  Set @DBName='Employee'

  Set @MaxId ='select max(empId) as Id into ##table from ' + @DBName + ' ..Employee'

  exec(@MaxId)

  Select @ID = Id + 1 from ##table

  drop table ##table

  Select @ID