T-SQL: Variable Scope

Question

I am trying to store the results of an SQL query into a variable.The query simply detects the datatype of a column, hence the returned result is a single varchar.

SET @SQL = 
    'declare @@x varchar(max) SET @@x = (select DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE Table_name = ' +char(39)+@TabName+char(39) +
    ' AND column_name = ' +char(39)+@colName+char(39) + ')'
EXECUTE (@SQL)

Anything within the 'SET declaration' cannot access any variables outside of it and vice versa, so I am stuck on how to store the results of this query in a varchar variable to be accessed by other parts of the stored procedure.

Answer 1

You dont need a dynamic query to achieve what you want, below query will give the same result as yours.

  declare @x varchar(max)
  declare @tableName varchar(100), @ColumnName varchar(50)

  set @tableName = 'Employee'
  set @ColumnName = 'ID'

  select @x = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
  where
     Table_Name = @tableName
     and column_name = @ColumnName

  select @x