locked
@ variables as field names RRS feed

  • Question

  • User1210767569 posted

    In SQL Server Management Studio I have the below code. If I set @vAreaName in the update to "Fieldname" it works but I can not get it to work as a variable. I've tried TRIM and evens caps, stumped.

    USE DATABASE 
    
     
    DECLARE @vAreaName As varchar(max)
    DECLARE @vStatus As varchar(max)
    DECLARE @EmployeeID As varchar(max)
    
    SET @vAreaName = 'FIELDNAME'
    SET @vStatus = '2'
    SET @EmployeeID = '36'
     
    
    UPDATE [TblTable]
    SET @vAreaName = @vStatus
    WHERE EmployeeID =  @EmployeeID

    Monday, April 25, 2016 1:07 PM

Answers

  • User77042963 posted
    --Create table TblTable (EmployeeID int, NOK varchar(100), FIELDNAME  varchar(100))
    Insert into TblTable values (36,'1','anthing')
    
    DECLARE @vAreaName As varchar(100)
    DECLARE @vStatus As varchar(100)
    DECLARE @EmployeeID As int
    
    SET @vAreaName = 'NOK'
    SET @vStatus = '2'
    SET @EmployeeID =  36 
     
      
    BEGIN
    
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = N'UPDATE TblTable SET  '+ QUOTENAME(@vAreaName) +' =  @vStatus    WHERE EmployeeID =  @EmployeeID';
    Print @SQL
     EXEC sp_executesql @SQL, N'@vAreaName varchar(100),  @vStatus varchar(100), @EmployeeID int', @vAreaName, @vStatus, @EmployeeID;
    END
    
    
    Select * from TblTable

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 25, 2016 2:00 PM

All replies

  • User1210767569 posted

    As a test I tried Exec sp_executesql but not quite there...

    USE DB
    
    DECLARE @vAreaName As varchar(max)
    DECLARE @vStatus As varchar(max)
    DECLARE @EmployeeID As varchar(max)
    
    SET @vAreaName = 'NOK'
    SET @vStatus = '2'
    SET @EmployeeID = '36'
     
      
    BEGIN
    
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = 'UPDATE TblTable SET ' + QUOTENAME(@vAreaName) + ' = ' + QUOTENAME(@vStatus)  + ' WHERE EmployeeID = ' + @EmployeeID;
    EXEC sp_executesql @SQL;
    END

    Monday, April 25, 2016 1:27 PM
  • User77042963 posted
    --Create table TblTable (EmployeeID int, NOK varchar(100), FIELDNAME  varchar(100))
    Insert into TblTable values (36,'1','anthing')
    
    DECLARE @vAreaName As varchar(100)
    DECLARE @vStatus As varchar(100)
    DECLARE @EmployeeID As int
    
    SET @vAreaName = 'NOK'
    SET @vStatus = '2'
    SET @EmployeeID =  36 
     
      
    BEGIN
    
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = N'UPDATE TblTable SET  '+ QUOTENAME(@vAreaName) +' =  @vStatus    WHERE EmployeeID =  @EmployeeID';
    Print @SQL
     EXEC sp_executesql @SQL, N'@vAreaName varchar(100),  @vStatus varchar(100), @EmployeeID int', @vAreaName, @vStatus, @EmployeeID;
    END
    
    
    Select * from TblTable

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 25, 2016 2:00 PM