Prompt for Parameters with SQL Management Studio

How to prompt an SQL query user to input information

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.

Prompt for parameters with SQL Management Studio

SSMS will prompt for parameters for a stored procedure, if you right click the proc and select "EXECUTE". Otherwise, no, it will not prompt for parameters.

SQL Server Management Studio: prompt for user input

I recommend writing a small application to handle this. I am not aware of any way to prompt the user for input when running sql. IMO Sql Management studio is not an environment for running scripts that require user input

How to show dialog for getting value of parameter in SQL Server?

You probably want template parameters:

SELECT *
FROM tbl
WHERE Name = <Name, VARCHAR(50),'default_value'>;

Docs:

The Specify Values for Template Parameters dialog is a grid with three columns. The Parameter and Type columns are read-only and cannot be changed. Review the contents of the Value column, and change any of the defaults to values appropriate for your implementation.

To use this dialog box, you must have parameters in your script enclosed in angle brackets (< >) in the format: parameter_name, data_type, default_value.

When you run your code you will get windows like:

^{Image source: https://www.red-gate.com/simple-talk/wp-content/uploads/blogbits/david.atkinson/Using-a-SQL-Prompt-snippet-for-easy-data_D810/TemplateParams_thumb_3.png}

SQL - User input in query

Here's the code for a stored procedure:

CREATE PROCEDURE SomeName(@UserStart DATETIME, @UserEnd DATETIME) 
AS BEGIN

SELECT somestuff
FROM sometable
WHERE somedate BETWEEN @UserStart AND @UserEnd

END

---

Related Topics