Solved

SQL Query - Dynamic Field

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

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 39

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

19 Experts available now in Live!

Get 1:1 Help Now