USING A DYNAMIC VARIABLE

• Chris Jones-133875

  SSC Enthusiast

  Points: 141

  More actions

  August 26, 2004 at 2:57 am

  #89127

  hey guys, just a quick question:

  declare @set varchar(2000)

  set @set = ' DECLARE @employee_id int

  SELECT @employee_id = employee_id

  FROM ##employee

  WHERE code = ''AA'''

  SELECT @employee_id '

  if you exec this, it gives me a number eg. 65656

  how do i now , use this 'output' and assign it to a variable?

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  August 26, 2004 at 5:32 am

  #520674

  How bout

  DECLARE @employee_id

  SET @employee_id = (SELECT employee_id FROM ##employee WHERE code = 'AA')

  SELECT * FROM table1 WHERE fieldx = @employee_id

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• g-168250

  Valued Member

  Points: 70

  More actions

  August 27, 2004 at 1:11 am

  #520813

  Use a SP with "output" ?

  drop table ##employee

  go

  select 'AA' as code,123 as employee_id into ##employee

  go

  drop procedure p1

  go

  create procedure p1 @Code varchar(10),@@employee_id int output as

   SELECT @@employee_id = employee_id

   FROM ##employee

   WHERE code = @Code

  go

  declare @@emp_id int

  exec p1 'AA', @@emp_id output

  select @@emp_id

  --G--

• Karl Klingler

  SSCertifiable

  Points: 5869

  More actions

  August 27, 2004 at 2:06 am

  #520818

  perhaps something like that?

   

  set @max-2 = 0

  set @sql = 'SELECT @max-2 = max(ordinal_position) FROM tempdb.information_schema.columns WITH (NOLOCK) where table_name = '''+@temp_table + '2'''

  EXEC sp_executesql @sql, '@max int OUTPUT', @max-2 OUTPUT

  if @max-2 is not null print 'max: '+str(@MAX) else print 'max: null'

  karl

  Best regards
  karl