How do you take input values from users in T-SQL?

Question

I want to know how to take input from users in T-SQL.

For example, how would I do a program which takes two numbers from user and adds them together?

Answer 1

Never ever do this. Pass those two values as arguments to function/sproc (let alone it's plain weird to use SQL Server as a calculator).

Answer 2

There are a number of options, including:

1) Create a stored procedure that takes in the values supplied to you by the user

2) Dynamically create a Select statement based on the values supplied by the user

In reality to answer this properly you would need to provide us more information e.g. how are you going to obtain the information from the user, how do you intend to query the database.

Answer 3

A layered architecture that separated user interface from persistence would never do this. Persistence code should never be that close to users. There's no chance for validation doing it that way.

The example might be contrived, but the answer is the same: don't.

Answer 4

There are any number of ways to build a UI that can accept the user input. That may be the subject of another question.

As other commenters note, do consider the security risk in accepting user input directly, it is critical to sanitize data at multiple layers of the application. That said all data driven applications must accept and act upon user data. This is not impossible, it is just important that you have a full understanding of the risks. The potential problem with accepting user data and executing on it, is that there is the risk of a nefarious user to attempt to execute their own code on your database and drop tables, extract sensitive data or other nasty things. Unless the whole application is behind a coprorate firewall, this is not a task for anybody without significant security experience.

That said, the simplest UI would be to have an internal and trusted user install something like SQL server management studio and then give them .sql scripts that call a stored procedure. This technique is only something that I could reccomend for a trusted user that is somewhat technical.

you create a procedure like this:

---

create procedure AddNumbers
    @augend int,
    @addend int
as
/* 
if all the input that you are accepting is numeric 
it is somewhat safer as long as you use int or numeric datatypes from the start.
I would stay away from using any alphanumeric data in a function like this 
without running it through some validation with a regular expression or other method.
*/
select @augend+@addend

---

and then instruct you user to call it like this

---

execute AddNumbers
    @augend = 4,
    @addend = 5

---

further you could build a UI that calls this procedure and gives the result. But you will need to carefully condder the access and security needed.