Pass (Send) Integer variable as Column Name to Select statement in SQL Server

Last Reply 6 months ago By dharmendr

Posted 6 months ago

Hi all, i have an issue with select statement in SQL query The below table is my sql table.

CREATE TABLE [dbo].[TT_Screen](
    [Rack_Id] [int] NOT NULL,
    [Row_Name] [char](1) NOT NULL,
    [1] [varchar](5) NULL,
    [2] [varchar](5) NULL,
    [3] [varchar](5) NULL,
    [4] [varchar](5) NULL
) ON [PRIMARY]

The table values are like below

Rack_Id Row_Name [1] [2] [3] [4]
9 A 1 0 3 0
9 B 0 2 0 4
9 C 0 0 3 0
9 D 0 2 3 4

@Column means [1],[2],[3],[4] these are not fixed, we pass the column name at the time of select statement 

@Rack_Id=9

@Row_Name=B

@Column=3 by using three parameters i want to return that cell value 0.

if the parameters are

@Rack_Id=9

@Row_Name=C

@Column=3 then i want to return that cell value 3.

my query was wrong, but i am sharing this query.

SELECT @Column as Rack_Loc 
FROM TT_Screen 
WHERE Rack_Id=@Rack_Id AND Row_Name=@Row_Name

Thanks in Advance.

Posted 6 months ago

Hi nagaraju60,

You have to execute using Dymanic query.

Check the below sample query.

SQL

CREATE TABLE TT_Screen(
[Rack_Id] [int] NOT NULL,
[Row_Name] [char](1) NOT NULL,
[1] [varchar](5) NULL,
[2] [varchar](5) NULL,
[3] [varchar](5) NULL,
[4] [varchar](5) NULL
)

INSERT INTO TT_Screen VALUES(9,'A',1,0,3,0)
INSERT INTO TT_Screen VALUES(9,'B',0,2,0,4)
INSERT INTO TT_Screen VALUES(9,'C',0,0,3,0)
INSERT INTO TT_Screen VALUES(9,'D',0,2,3,4)

DECLARE @Rack_Id VARCHAR(10), @Row_Name char(1), @Column varchar(5)
SET @Rack_Id = 9
SET @Row_Name = 'B'
SET @Column ='3'
DECLARE @DynQuery nvarchar(1000)
SET @DynQuery = N'SELECT [' + @Column + '] 
				FROM TT_Screen 
				WHERE Row_Name = '''+@Row_Name+''' AND Rack_Id = '''+@Rack_Id+''''
EXEC (@DynQuery)

GO

DECLARE @Rack_Id VARCHAR(10), @Row_Name char(1), @Column varchar(5)
SET @Rack_Id = 9
SET @Row_Name = 'C'
SET @Column ='3'
DECLARE @DynQuery nvarchar(1000)
SET @DynQuery = N'SELECT [' + @Column + '] 
				FROM TT_Screen 
				WHERE Row_Name = '''+@Row_Name+''' AND Rack_Id = '''+@Rack_Id+''''
Exec (@DynQuery)

Output

For first query output is 0.

For second query output is 3.