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

• Lowell

  SSC Guru

  Points: 323522

  More actions

  March 23, 2006 at 3:17 pm

  #628475

  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

  March 23, 2006 at 4:06 pm

  #628484

  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: 1004595

  More actions

  March 23, 2006 at 5:32 pm

  #628491

  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

   

  --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)