How to use parameters in select query?

Question

I have table :

Table Name : tbl_Income

EmployeeID      Element      FinancialYear   Jan    Feb    Mar

00402060         Basic         2016-2017     100    200    300
00402060         HRA           2016-2017     100    200    300
00402060         DA            2016-2017     100    200    300

In which i want to fetch data from tbl_Income.

In which i fetch below problem.

Declare @Month varchar(10) = 'Jan'

select @Month  from tbl_Income where  EmployeeID = '00402060' and Element = 'Basic' and  FinancialYear = '2016-2017'

I want to below Output

OUTPUT : 

   Jan
1  100

Please help me...

You'll have to do that with dynamic SQL. This isn't a very good database design, by the way. — HoneyBadger, Jan 20 '17 at 09:56

Gurwinder Singh · Accepted Answer · 2017-01-20T16:28:46.080

2

You can use Dynamic SQL like this:

declare @sql nvarchar(max)
Declare @Month varchar(10) = 'Jan'
declare @income int

set @sql = 'select @inc=' + @Month + ' from tbl_Income where  EmployeeID = ''00402060'' and Element = ''Basic'' and  FinancialYear = ''2016-2017'''

exec sp_executesql @sql, N'@inc int OUTPUT', @inc=@income OUTPUT
select @income as income

Beware though that this is open to SQL Injection attack.

You'd be better off fixing your design.

edited Jan 20 '17 at 16:28

answered Jan 20 '17 at 10:00

Gurwinder Singh

38,557
6
51
76

how to store this output '100' value in variable?? please help me..@GurV – Dhaval Purohit Jan 20 '17 at 10:07
can u check my answer i stored that value in one varible..@DhavalPurohit – Chanukya Jan 20 '17 at 10:11
@Chanukya - Your answer is exactly the same as mine. Dhaval - Look at [this](http://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable-for-sql-server) – Gurwinder Singh Jan 20 '17 at 10:14
Yes this is same answer...Mr.GurV I want to store this output value in one single variable like "declare XVariable decimal(18,2) " and now i want to this output store in XVariable... – Dhaval Purohit Jan 20 '17 at 10:17
@GurV he wrote select query but i did this in dynamic query please check – Chanukya Jan 20 '17 at 10:18

score 1 · Answer 2 · edited Jan 20 '17 at 17:48

1

Declare @Month varchar(10) = 'Jan'
declare @v nvarchar(max)
declare @v1 INT
set @v =CONCAT('select @v1=' ,@month, ' from 
table_a where  EmployeeID = ''00402060'' and Element = ''Basic'' and  FinancialYear = ''2016-2017''') 
PRINT @V
EXECUTE sp_executesql @v,N'@V1 INT OUTPUT', @V1=@V1 OUTPUT;  
SELECT @V1;

edited Jan 20 '17 at 17:48

Gurwinder Singh

38,557
6
51
76

answered Jan 20 '17 at 10:05

Chanukya

5,833
1
22
36

score 1 · Answer 3 · edited Jan 20 '17 at 10:58

[Sample Database Design][1]

[1]: https://i.stack.imgur.com/2vxEb.png -- table "dbo.Month"

    CREATE TABLE dbo.Month (
  Id int NOT NULL,
  Name nvarchar(50) NOT NULL,
  CONSTRAINT PK_Month PRIMARY KEY CLUSTERED (Id)
)

-- table IncomeType"

    CREATE TABLE dbo.IncomeType (
  Id int NOT NULL,
  Name nvarchar(50) NOT NULL,
  CONSTRAINT PK_IncomeType PRIMARY KEY CLUSTERED (Id)
)

-- table "FinancialYear"

    CREATE TABLE dbo.FinancialYear (
  Id int NOT NULL,
  YearSpan nvarchar(50) NOT NULL,
  CONSTRAINT PK_FinancialYear PRIMARY KEY CLUSTERED (Id)
)

-- table "Employee"

   CREATE TABLE dbo.Employee (
  Id int NOT NULL,
  Name nvarchar(50) NOT NULL,
  CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (Id)
)

-- table "Income"

    CREATE TABLE dbo.Income (
  Id int NOT NULL,
  EmployeeId int NOT NULL,
  TypeId int NOT NULL,
  YearSpanId int NOT NULL,
  MonthId int NOT NULL,
  CONSTRAINT PK_Income PRIMARY KEY CLUSTERED (Id)
)

--Alter Each Table to add foreign key references

ALTER TABLE Income
  ADD CONSTRAINT FK_Income_Employee FOREIGN KEY (EmployeeId) REFERENCES Employee (Id)

    ALTER TABLE dbo.Income
  ADD CONSTRAINT FK_Income_FinancialYear FOREIGN KEY (YearSpanId) REFERENCES dbo.FinancialYear (Id)


    ALTER TABLE dbo.Income
  ADD CONSTRAINT FK_Income_IncomeType FOREIGN KEY (TypeId) REFERENCES dbo.IncomeType (Id)

    ALTER TABLE dbo.Income
  ADD CONSTRAINT FK_Income_Month FOREIGN KEY (MonthId) REFERENCES dbo.Month (Id)

EmployeeID will come from the Employee table
Element will come from the income type table
FinancialYear --from the financial year table
MonthId from the months table

Now a simple join can help you get each value using the foreign key references

How to use parameters in select query?

3 Answers3