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

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

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.