using Dynamic SQL to get results from one table to use in another

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  November 9, 2005 at 7:10 am

  #167002

  Can anyone help please, I want to use dynamic sql to get results from one table to use in another query

  this works :-

  set @sdate1 = '09/11/2005'

  set @sVar = (select [Mick] from table1 where [date] = @sDate1)

  this fills the variable @sVar with the results I need

   

  but as I need to change the field name  Mick each time I use the stored procedure I was looking for a dynamic sql statement equilivent, can anyone help please

   

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  November 9, 2005 at 8:50 am

  #603038

  declare @sDate1 datetime

  declare @sqlstring varchar(1000)

  declare @ColName varchar(100)

  set @sDate1 = '09/11/2005'

  set @ColName = [Mick]

  set @sqlstring = 'select @sVar = ' + @ColName + '

  from table1

  where [date] = @sDate1'

  sp_executesql @sqlstring,N'@sDate1 datetime, @sVar varchar(100) OUTPUT, @sDate1, @sVar OUTPUT

  select @sVar

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  November 10, 2005 at 2:43 am

  #603176

  many thanks for the reply, I've managed to achieve what I wanted thanks to you by modifying your example, I've posted below the code I'm using

   

  declare @status varchar

  declare @Date1 varchar(10)

  declare @CMD Nvarchar(100)

  declare @Colname as varchar(100)

  set @colname = 'Ajit Jiwan'

  set @Date1 = '01/02/2006'

  SET @CMD = 'SELECT [' + @Colname + '] from [table1]' + ' where date = ''' + @Date1 + ''''

  print @CMD

  exec sp_executesql @CMD,N'@In varchar out',@Status out 

  print @status

   

  Thanks again

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  November 10, 2005 at 8:53 am

  #603256

  Oop's sorry, slight mistake, the solution I posted didn't return the value in @status

  Below is the working version

  declare @status nvarchar

  declare @sDate1 varchar(10)

  declare @CMD Nvarchar(100)

  declare @ColName as varchar(100)

  set @ColName = 'Ajit Jiwan'

  set @sDate1 = '01/02/2006'

  SET @CMD = 'SELECT @Rtn = [' + @ColName + '] from [Resource]' + ' where date = ''' + @sDate1 + ''''

  exec sp_executesql @CMD,N'@Rtn varchar (10) out,@Name1 varchar',

     @Status out,

     @sDate1

  print  @status