Avatar of mathburg
mathburg asked on

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?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
Jim Horn

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.
Aneesh

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

How do you run that query?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
geek_vj

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
Zberteoc

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

So, how's it goin, eh?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
mathburg

I'm trying to run in Management studio.
Jim Horn

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

Jim Horn

>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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
mathburg

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.
ASKER
mathburg

maybe I just write the update statement

update table
set group = ' /*insert group name here*/'
from table
where code = '/* insert ungrouped code here */'
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
mathburg

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Horn

>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.
ASKER
mathburg

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.
ASKER
mathburg

Control means that he wants to run  and upkeep the queries.  I understand the risk issue for me.  Thanks for your help and concern.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim Horn

Thanks for the accept, but why the B grade?
ASKER
mathburg

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?
ASKER
mathburg

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Horn

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.
Anthony Perkins

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?
ASKER
mathburg

what do you mean?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Anthony Perkins

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?