• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

How do I select a particular field by using a parameter

Hi,

I need to create a report where the user specifies which month to report on. I have the following basic query:

SELECT CODE, DESCRIPTION, MONTH_01, MONTH_02, MONTH_03, MONTH_04, MONTH_05, MONTH_06
FROM GLACC

I want the user to be able to specify whether they want month_01 or month_02 etc. So if they chose MONTH_03 the report would only show CODE, DESCRIPTION, MONTH_03.

Thanks!
0
erica686
Asked:
erica686
2 Solutions
 
nkhelashviliCommented:
Where are you trying to do it from?   From .Net or  ?
0
 
Pratima PharandeCommented:
I tried like this  ans works


Declare @ID varchar(20)

Set @ID = 'FIELD_NAME'

Select @ID from TABLE_NAME
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Create a procedure like the one below and pass your MONTH_xx as parameter.

And you will be able to get your results accordingly.
CREATE PROCEDURE ( @month varchar(40))
AS
DECLARE @sql nvarchar(1000);
SET @sql = N'SELECT CODE, DESCRIPTION, ' + @month + N'FROM GLACC';
 
CREATE TABLE #temp ( query_id int, query text, sqlquery text, xtype varchar(10));
 
INSERT INTO #temp
EXEC sp_executesql @sql;
 
SELECT * FROM #temp;
go

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
dqmqCommented:

Select code, description, amount from
(
select code, description, 1 as [month], Month_01 as amount from  GLACC union
select code, description, 2 as [month], Month_02 as amount from  GLACC union
select code, description, 3 as [month], Month_03 as amount from  GLACC union
select code, description, 4 as [month], Month_04 as amount from  GLACC union
select code, description, 5 as [month], Month_05 as amount from  GLACC union
select code, description, 6 as [month], Month_06 as amount from  GLACC
)
where [month] = 3
0
 
nkhelashviliCommented:
CREATE PROCEDURE MySelect (@Month varchar(10))
AS

SELECT CODE, DESCRIPTION,
(CASE WHEN @month= 'MONTH_01' THEN MONTH_01,
WHEN @month= 'MONTH_02' THEN MONTH_02,
WHEN @month= 'MONTH_03' THEN MONTH_03,
WHEN @month= 'MONTH_04' THEN MONTH_04,
WHEN @month= 'MONTH_05' THEN MONTH_05,
WHEN @month= 'MONTH_06' THEN MONTH_06
END
) AS MyMonth
FROM GLACC
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
nkhelashvili,
   Your code will end up in giving values like

CODE, DECRIPTION, MyMonth

x, x, MONTH_01
Y, Y, MONTH_O2

like that. It wont give you the desired result set.
0
 
nkhelashviliCommented:
rrjegan17, why do you think so? why it wil give such results?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
I got confused because of the incorrect syntax:
Sure your logic will work And corrected one is given below ..
CREATE PROCEDURE MySelect (@Month varchar(10))
AS
 
SELECT CODE, DESCRIPTION,
(CASE WHEN @month= 'MONTH_01' THEN MONTH_01
WHEN @month= 'MONTH_02' THEN MONTH_02
WHEN @month= 'MONTH_03' THEN MONTH_03
WHEN @month= 'MONTH_04' THEN MONTH_04
WHEN @month= 'MONTH_05' THEN MONTH_05
WHEN @month= 'MONTH_06' THEN MONTH_06
END
) AS MyMonth
FROM GLACC

Open in new window

0
 
nkhelashviliCommented:
oops commas  :( thank you
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
angelIII,
   We never received a response from erica686.

And based upon the problem definition, I would recommend

Split between 23608874 and 23608799

Comment 23608799 contains Logic but with Syntax mistakes.
Comment 23608874 contains working code..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now