Prompt user in SQL SERVER

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?
mathburgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Aneesh RetnakaranDatabase AdministratorCommented:
You need to create a user interface using access or .net or some front end language for this, with ssms this is not possible
0
ZberteocCommented:
How do you run that query?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

geek_vjCommented:
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
0
ZberteocCommented:
We still don't know if he runs the query in Management Studio or some other software. Question is not clear about this.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So, how's it goin, eh?
0
mathburgAuthor Commented:
I'm trying to run in Management studio.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
mathburgAuthor Commented:
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.
0
mathburgAuthor Commented:
maybe I just write the update statement

update table
set group = ' /*insert group name here*/'
from table
where code = '/* insert ungrouped code here */'
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I can write code to show that it is missing and then I can write an update statement to input,
Yes, you can always write a conditional UPDATE using CASE blocks, WHERE clauses, maybe an a LEFT JOIN b ON a.id = b.id ... WHERE b.id IS NULL.  

>I was just wandering if I can streamline that process to the user
Explain in clear and colorful language why the user would need to be directly involved.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mathburgAuthor Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
mathburgAuthor Commented:
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.
0
mathburgAuthor Commented:
Control means that he wants to run  and upkeep the queries.  I understand the risk issue for me.  Thanks for your help and concern.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the accept, but why the B grade?
0
mathburgAuthor Commented:
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?
0
mathburgAuthor Commented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Anthony PerkinsCommented:
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?
0
mathburgAuthor Commented:
what do you mean?
0
Anthony PerkinsCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.