How to prompt an SQL query user to input information

Question

I have a query that I wrote in SQL server that will be run mainly by people who don't know SQL, and there are two areas that have to have a different string or date entered each time the query is run. As of right now, I just wrote it so that you enter the information at the top of the query and its stored as a variable. Is there a way that I can get SQL to prompt the person running the query to enter the data? below is an excerpt of the code that has what I am talking about in it.

declare 
/*ENTER ACCOUNTING MONTH*/     
   @amon VARCHAR(2) = '05',
/*ENTER INVOICE DATE IN MM/DD/YYYY FORMAT*/
   @invdate DATE = '05/31/2015'
~~
rest of the code
~~
declare @sumA numeric(25, 5), @sumB numeric(25, 5), @ratio numeric(25, 5)
select @sumA = sum(amnt) from accnt where accno = '1152'
select @sumB = sum(amnt) from acc1152
update acc1152 set amnt = amnt * (@sumA/@sumB),
amon = @amon,
invdate = @invdate,
ven = '1152',
code = '1152',
invno = 'INVENTORY'

so is it possible for SQL to prompt the user to type in the value for @amon and @invdate? other than me just having the comment line telling them to do so?

It will be very funny if user will enter 'drop database databaseName'. make an application that will prompt the input from user... — Giorgi Nakeuri, Jun 25 '15 at 13:37
that would be funny, but it would be evident who did it, so I'm not worried about that happening. and what kind of application? the only real coding knowledge I have is basic SQL. — Vbasic4now, Jun 25 '15 at 13:41
No it is not possible. SQL Server is NOT a front end. It is a database server. This isn't access. If you want a prompt it has to be from an application. — Sean Lange, Jun 25 '15 at 13:43
ok thank you. Any suggestions where I should start as far as that goes? — Vbasic4now, Jun 25 '15 at 13:47

Giorgi Nakeuri · Accepted Answer · 2015-06-25T13:54:17.560

In case you can not do an application, you have no developers etc etc, you have one way - make a stored proc:

create stored procedure spDoSomeJob
@amon VARCHAR(2),
@invdate DATE
as
begin

    ~~
    rest of the code
    ~~
    declare @sumA numeric(25, 5), @sumB numeric(25, 5), @ratio numeric(25, 5)
    select @sumA = sum(amnt) from accnt where accno = '1152'
    select @sumB = sum(amnt) from acc1152
    update acc1152 set amnt = amnt * (@sumA/@sumB),
    amon = @amon,
    invdate = @invdate,
    ven = '1152',
    code = '1152',
    invno = 'INVENTORY'

end

Deny any activity permissions for users except just running this procedure. Execute it like:

exec spDoSomeJob  @amon = '05', @invdate = '05/31/2015'

At least you will be sure that no user can occasionally corrupt something... And if you will not supply values to parameters of stored procedure it will prompt you to do this unless you have no default values for those parameters. It seems to me like the best workaround for your case.

If you instruct the user to right click on the stored procedure and click Execute Stored Procedure... then the user will be prompted with a nice GUI window to see and enter all the required parameters for the stored procedure. https://stackoverflow.com/a/52107123/8595398 — Matthew, May 13 '19 at 00:46

Alex · Answer 2 · 2015-06-25T13:47:26.833

3

Could you perhaps call this query from a different programming language? SQL is not effective for the task you are describing. A high-level language such as Python/Java/C# would allow you to easily prompt for user input and would arguably be more suited to the job.

If you really want to do something in SQL and they're using SSMS then you can use SSMS templates and let the users enter the parameter values using CTRL+SHIFT+M, although I would discourage this approach.

edited Jun 25 '15 at 13:47

answered Jun 25 '15 at 13:38

Alex

5,364
9
54
69

I am running this Query through AnySQL Maestro for databases, is it possible to use another language with that program? if so, I am willing to try it. Its just that the only real coding knowledge I have is SQL, and its pretty limited – Vbasic4now Jun 25 '15 at 13:44
1

@samhatcher All you need is a language that has drivers for SQL Server, which will be most modern languages. AnySQL Maestro is simply a tool for connecting to and querying databases, so has no impact on the language you choose. – Alex Jun 25 '15 at 13:59

score 3 · Answer 3 · answered Oct 10 '19 at 23:37

I use TOAD for SQL Server, where you can define "runtime input fields" as part of your script.

i.e.

select * from myTable AS m where m.ID = :MyValue

:MyValue is your parameter, and when TOAD runs the SQL command, it will prompt the user to enter a value on the fly. It's a shame SSMS does not offer this feature.

score 2 · Answer 4 · answered Jun 25 '15 at 13:49

As others have mentioned, SQL Server and Management Studio are not intended as end-user tools.

Since you are using SQL SERVER, you have a tool (perhaps not installed and configured) called SQL Server Reporting Services (SSRS).

It does have the ability to prompt users for values for the parameters.

Your query goes into the Report Designer, the @ variables become report filters (pretty automatically) and you get to make some nice layout.

PAPER and PRINTING are not involved. In fact SSRS specializes in one report drilling into another, passing starting values for the next query.

In fact, SSRS would even love to consume the stored procedure you made in the interim based on the accepted answer ;-) — Stan, Jun 25 '15 at 13:51

score 2 · Answer 5 · answered Oct 03 '17 at 21:35

Another solution is to use PowerShell to ask the user for input and then pass that data to your script as it is run by PowerShell. On the user's computer, you may need to install some PowerShell extensions in order for them to run the TSQL. Like import-module sqlps. PowerShell is not for the faint of heart but it is free and so you could try it without a large commitment.

Stackoverflow Topic

How to prompt an SQL query user to input information

5 Answers5