Prompt user in SQL SERVER

SQL Server is a database, not a UI tool, so SQL does not have any capabilities to prompt a user for a value.

You'll have to do this in whatever front-end application you are using, then take that value and include it as a parameter in a SP, or part of a WHERE clause in a front-end-executed SQL statement.

You need to create a user interface using access or .net or some front end language for this, with ssms this is not possible

How do you run that query?

I want to prompt the user to input data that is missing when the query runs in Sql Server 2008, so they can complete missing data before the query completes.  Is this possible?
>> Not possible using sql server native tools, have to use other front end tools like .net etc

We still don't know if he runs the query in Management Studio or some other software. Question is not clear about this.

So, how's it goin, eh?

I'm trying to run in Management studio.

Won't matter.  As stated above, SQL Server is a database, not a UI tool, so SQL (and by extension SSMS) does not have any capabilities to prompt a user for a value.

About all you can do here is to declare a variable up top, hard-code it to a value, and then execute your script.

For example..

Declare @fruit varchar(10) = 'banana'

-- Your code goes here
SELECT * FROM fruit_bins WHERE fruit_type = @fruit

Select allOpen in new window

>I want to prompt the user to input data that is missing when the query runs in Sql Server 2008, so they can complete missing data before the query completes.  

Also, define for us 'missing data', preferably with a before-and-after mockup, as that could have 42 different meanings.

For instance I have a code that defines a group.  For instance Car and truck roll into Auto, but another code sneaks in called suv.  I can write code to show that it is missing and then I can write an update statement to input, I was just wandering if I can streamline that process to the user.  It sounds like the answer is no.

maybe I just write the update statement

update table
set group = ' /*insert group name here*/'
from table
where code = '/* insert ungrouped code here */'

Well I have about twenty or so queries that are for a department and that department head wants to be in control of those queries.  I tell him I'm there to help, but he wants me to make it easier on his employees to run and update the queries.  I don't really agree, but he's the cfo so he can do what he wants I guess

>department head wants to be in control of those queries.
Tell him to bite me.  Especially a CFO.  Well, maybe not just yet.

Exactly what is meant by 'control'?  Normally I'd flush this out, as it sounds like you can set up a table somewhere where users can make their own assigments, such as the group-code in the above comment.  Then have somebody build a UI where the user can edit it.

Then in your SQL, JOIN on this table such that if a row does not have a group, it looks to this table's code value to determine what it should be.

>department head wants to be in control of those queries.
Keep in mind you're playing with fire here if users have direct access to queries.  Especially if this data has any PHI / data privacy requirements, or auditing requirements that dictate an audit trail.

Not the solution I was looking for, but a good one.  I never thought about putting a subquery in my update statement..  Going to try it out.

Control means that he wants to run  and upkeep the queries.  I understand the risk issue for me.  Thanks for your help and concern.

Thanks for the accept, but why the B grade?

jimhorn: Exactly what is meant by 'control'?  Normally I'd flush this out, as it sounds like you can set up a table somewhere where users can make their own assigments, such as the group-code in the above comment.  Then have somebody build a UI where the user can edit it.


I already do this in a way.  I import tables to sql server.  What is a UI though?

I don't know why the b grade dude.  I'm sorry if you don't like.  It's just not what I was looking for but none the less good.

We'll let this one slide, but for future reference 'You can't do that' is an acceptable answer in EE if it is the correct answer.

I'm sorry if you don't like.  
It is not whether we like it or not.  This is EE, please abide by the guidelines you agree to every time you log on:
What grade should I award?
The experts told me “you can’t do that”. What do I do now?

what do you mean?

what do you mean?
What do I mean by what?  If you are asking what I mean about the EE Guidelines.  Every site that requires some sort of registration has rules that you should abide by when you log on.  It appears that you are not familiar with these basic rules and specifically the ones I included above.  All you have to do is click on the link and you will get an ample explanation of what Jim and I mean.  For example, if I click on the second link this is what I read:

The experts told me “you can’t do that”. What do I do now?
Last Updated: May 29, 2013 03:00PM PDT
Sometimes, the unfortunate truth is that what you’re asking isn’t possible. While “you can’t do that” is never what an asker wants to hear, that doesn’t make it any less true. If the experts tell you that what you’re trying to accomplish isn’t possible, they are still giving you a correct answer, so make sure you close your question properly by awarding points to the expert who provided this answer.

Now is there any part of that you do not understand?