x

how to get the value directly from the user in sql server

in oracle we can use &amp to get value directly from the user while in sql server how we get the value from the user

more ▼

asked Apr 25, 2012 at 10:56 AM in Default

honeyz2292 gravatar image

honeyz2292
10 2 3 3

Are you referring to prompting the user for input?
Apr 25, 2012 at 01:38 PM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If you are talking about the case where a user is about to run a stored procedure, then you can use paramaters. For a simple example, in :

create procedure fooBar
    @userValue varchar(50)

as
    select @userValue

GO

fooBar 'Test Data'

@userValue is the paramater.

If you mean you want to actually prompt the user for a value, then your best option is to provide some sort of front end in a language other than SQL, like [Python][1], C#, etc which calls back to SQL Server.

[1]: http://www.simple-talk.com/sql/database-administration/python-for-the-sql-server-dba/
more ▼

answered Apr 25, 2012 at 04:50 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

Apr 26, 2012 at 05:41 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

I do not know in which context you want the user prompt. If I compare SSMS of SQL Server with SQL PLUS of Oracle, then the functionality is not the same but certainly a prompt is possible but that will then require a manual execution of the script unlike SQL Plus where the execution is done automatically. The hack is from the TEMPLATE explorer. You can write a custom script in SSMS

SELECT * FROM YOURTABLE --COULD ALSO BE A USER PROMPT
WHERE COLUMNNAME = '<PARAMETERNAME, nvarchar(50), DEFAULTVALUE>' --USER PROMPT

where first the PARAMETERNAME is what you name the parameter, nvarchar(50) is where you put the datatype of the column and DEFAULTVALUE is what you want as the default value if no input to be provided.

After hitting Ctrl+Shift+M shortcut key combination, you would be prompted for the input. After providing the input, the values would be replaced in SSMS and you have to execute the script manually.

For more details you can search the TEMPLATE functionality on the internet as it has many more uses.

And If you want to run the stored procedure through SSMS and wanted to supply the parameters through user prompt, then Right Click on the stored procedure, Select Execute Procedure and you would be prompted for the input parameters.

Another option could be to use the combination of batch programming and sqlcmd utilty.

But if you required an actual user prompt then as TimothyAWiseman said, use some programming language.
more ▼

answered Apr 26, 2012 at 05:40 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x378

asked: Apr 25, 2012 at 10:56 AM

Seen: 1916 times

Last Updated: Apr 26, 2012 at 05:44 AM