Solved

User Input Parameter for SQL Query

Posted on 2008-10-27
10
499 Views
Last Modified: 2013-11-16
I need to create a very simple query.  I would like to return all the data from one table for a specific item.  I want the user to be prompted to enter the item id.  Below is an example of the query without letting my user enter the item.

Select * from inventory
where inventory.item_id = 'AQUA-12345'

I just want this to be generic so my user is prompted to enter the item they are looking for.
0
Comment
Question by:RDB1369
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
I am assuming SQL 2000 based upon the tags.

You would pass a parameter in to a procedure.


create procedure up_inventory_get
 @item_ID varchar(30)
as
select * from inventory
where item_id = @item_id
go


to call it:

exec up_inventory_get @item_ID = 'AQUA-12345'
0
 
LVL 9

Expert Comment

by:jamesgu
Comment Utility
from where do you run the query?

basically, you need to prompt to the user and get the input, and then bind the parameter in the below query

Select * from inventory
where item_id = ?

0
 

Author Comment

by:RDB1369
Comment Utility
This is exactly what I have done so far, and yes you are right I am working in SQL 2000.  
However, I really wanted my user to be able to just exec the stored procedure and be asked the question  "Enter Item",   Is this possible?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
No.  
0
 

Author Comment

by:RDB1369
Comment Utility
I am sorry I did not see the second comment when I posted my response.  I have written a procedure like Brandon suggested and I can use that, but I wanted to just have my user type in
          Exec Up_inventory_get
and then the first part of the procedure would prompt my user to enter the correct item ID.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 9

Expert Comment

by:jamesgu
Comment Utility
i don't think you can do that since stored procedure is server-side object,

what you may do is to create a .bat (if it's in windows environment) or a shell script to achieve that
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
Comment Utility
I think it's time to close this question up.

You said you used a procedure like http:#22815829 and I answered your question about prompting for input in http:#22815907
0
 

Author Closing Comment

by:RDB1369
Comment Utility
I guess this is as good as it gets
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
Why the B grade?  Unfortunately, you can't do what it is you are asking to do.  I answered your question completely and accurately.

I'd like you to reconsider the grade based upon the grading suggestions here (copied from help):

What's the right grade to give?

Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.

Giving a higher grade has no impact on your Available Points.
0
 
LVL 1

Expert Comment

by:Vee_Mod
Comment Utility
No Response to Expert and/or Moderator.
Force changed.
 
Vee_Mod
Experts Exchange Moderator
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

12 Experts available now in Live!

Get 1:1 Help Now