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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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.