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

in oracle we can use & 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

avatar image

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)
     select @userValue
 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, C#, etc which calls back to SQL Server.

more ▼

answered Apr 25, 2012 at 04:50 PM

avatar image

15.6k 22 57 38

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


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

avatar image

Usman Butt
14.9k 6 13 21

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Apr 25, 2012 at 10:56 AM

Seen: 6239 times

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

Copyright 2018 Redgate Software. Privacy Policy