Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Query - Dynamic Field

Posted on 2012-03-19
4
179 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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

860 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