Prompt for User Input to set variable

Question

Prompt for User Input to set variable

jim Edwards-308192

Say Hey Kid

Points: 680
More actions
March 23, 2006 at 2:22 pm

#113341

Is it possible to display a prompt a user for input (set variable) so I can write one sql to do many different things..
For example: Enter Month (01-12): 12 (12 is user input)
Then how would I reference that input to do a comparison in my where statement as follows:
datepart(dd,hire_date) = value entered in prompt statement

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

not directly from TSQL, no; you'd have to write a program that would prompt for input, and then use the input in the sql to be performed;

Oracle (or rather, SQL PLUS) will do that when it encounters the & symbol unless you had SET DEFINE OFF; command.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jim Edwards-308192 Say Hey Kid Points: 680 More actions · Answer 2

How do I reference a static variable in a where statement in T-SQL. No user input, variable always = 12

then how would my datepart(dd,hire_date) command by set up?

thanks

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Actually, you can... well, sort of... make a call to xp_CmdShell and run a VB Script to ask the question and then pass it to a stored procedure.

It's a lot easier to do this in a batch file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions