sql server - How to get the data from below sql table?

Question

My Table structure is

Value  0   1   2
  X    c   a   d
  Y    d   b   e
  Z    e   c   f

my sql query is

Declare @CharValue Char(1)='c';
Declare @CharIndex int=0;
Select [Column] from Data_Masking where '['+Cast(@CharIndex as varchar(5))+']'= 'c'

I need output as 'X' from my above query but its returns empty.

question from:https://stackoverflow.com/questions/65843929/how-to-get-the-data-from-below-sql-table

Answer 1

You can't replace a column's name, which needs to be a literal, with a variable. You could do this with dynamic SQL, but unless you really know how to use it, I don't recommend you go down that route.

Considering the simplicity of your table, I suggest a "few" OR clauses:

SELECT [Value]
FROM dbo.YourTable
WHERE (@@CharIndex = 0 AND [0] = @CharValue)
   OR (@@CharIndex = 1 AND [1] = @CharValue)
   OR (@@CharIndex = 2 AND [2] = @CharValue)
OPTION (RECOMPILE);

I really, as well, recommend better names that 0, 1 and 2. Don't use object names that require delimit identification.

db<>fiddle

If you "must" do this with dynamic SQL, you would need to do the following. Note, however, that using dynamic SQL comes with a lot of considerations. If you do not understand the security implications of dynamic SQL, you should not be using this. In truth, I would suggest you should be changing your design:

DECLARE @CharValue char(1) = 'c',
        @CharIndex sysname = N'0';

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT [Value] AS [ColumnValue] FROM dbo.YourTable WHERE ' + QUOTENAME(@CharIndex) + N' = @CharValue;';
--PRINT @SQL;
EXEC sys.sp_executesql @SQL, N'@CharValue char(1)', @CharValue;

DB<>Fiddle