Solved

SQL Query - Dynamic Field

Posted on 2012-03-19
4
180 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
[X]
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
  • 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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