Solved

SQL Query - Dynamic Field

Posted on 2012-03-19
4
172 Views
Last Modified: 2012-03-19
I need help with a query.  I would like to add in a parameter that allow user to select a field dynamically.

Here's the query.

DECLARE @ID VARCHAR(2)
DECLARE @ITEM_ID VARCHAR(30)

SET @ID = '1'
SET @ITEM_ID = '09901'

SELECT ITEM_ID, DESCRIPTION, USER_+@USER+
FROM  ITEM_MASTER
WHERE ITEM_ID  = @ITEM_ID

I would like for user to be able o toggle from 1 to 10 with the user field.
0
Comment
Question by:holemania
  • 2
4 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37738923
DECLARE @ID VARCHAR(2)
DECLARE @ITEM_ID VARCHAR(30)
declare @sql varchar(max)

SET @ID = '1'
SET @ITEM_ID = '09901'

set @sql = 'SELECT ITEM_ID, DESCRIPTION, USER_ ' +@ID+  ' FROM  ITEM_MASTER WHERE ITEM_ID  = ' + @ITEM_ID

execute (@sql)
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 37739007
have you tried making a stored procedure
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 37739034
Just noticed a slight error.  You need ' ' around the item id.

set @sql = 'SELECT ITEM_ID, DESCRIPTION, USER_ ' +@ID+  ' FROM  ITEM_MASTER WHERE ITEM_ID  = ' + @ITEM_ID

should be

set @sql = 'SELECT ITEM_ID, DESCRIPTION, USER_ ' +@ID+  ' FROM  ITEM_MASTER WHERE ITEM_ID  = ''' + @ITEM_ID + ''''


A stored proc wouldn't help with the dynamic portion of this, but would help in terms of keeping the parameters straight.
0
 

Author Closing Comment

by:holemania
ID: 37739281
Thank you.  That's exactly what I need.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 74
sql calculate averages 18 47
Syntax error [Missing Operator] in query expression 7 31
SQL Query resolving a string conversion issue 26 38
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now