We help IT Professionals succeed at work.

How do I select a particular field by using a parameter

erica686
erica686 asked
on
Medium Priority
172 Views
Last Modified: 2012-05-06
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!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Where are you trying to do it from?   From .Net or  ?
CERTIFIED EXPERT

Commented:
I tried like this  ans works


Declare @ID varchar(20)

Set @ID = 'FIELD_NAME'

Select @ID from TABLE_NAME
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Commented:

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
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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.
CERTIFIED EXPERT

Commented:
rrjegan17, why do you think so? why it wil give such results?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
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

CERTIFIED EXPERT

Commented:
oops commas  :( thank you
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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..
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.