prompt box to allow variable date entry

[rdrunner400]

Hi Guys,

What I have here is a exec command to run on a SQL Server and I am wanting to be able to have the 2012-06-25 to be a variable that upon running this stored procedure a prompt box appears to allow a variable date to be entered.

Is this possible and if so please can you show me an example.

exec('CREATE VIEW "Runs" AS
SELECT "Run_ID" FROM "Run" WHERE
"Completed" <= ''2012-06-25''');


Regards

rdrunner

 

[bborissov]

CREATE PROCEDURE MyRuns (@DateCompleted datetime)
AS
BEGIN
     SELECT Run_ID
         FROM Run
     WHERE Completed < DATEADD(dd,1,@DateCompleted)
END
GO

EXEC MyRuns @DateCompleted  = '20120625'

Borislav Borissov
VFP9 SP2, SQL Server

 

[rdrunner400]

Hi,

So by doing this code I am wanting a prompt box to appear so that I can enter in any date and then carry on with this date in the relevant procedure that creates the view.

rdrunner

 

[DaveInIowa]

The closest solution I can think of would be to create a template ...

ALTER VIEW Runs
AS
SELECT Run_ID
  FROM Run
 WHERE '<Completed On or Before, DATE, Enter date here>' >= Completed

Then select 'Specify Values for Template Parameters' (under the [bold]Query[/bold] menu option or [bold]Ctrl+Shift+M[/bold]). This function is also available in the [bold]SQL Editor[/bold] toolbar.

Not as nice a solution as one would like since you have to hit Enter to replace the template parameter values and then F5 to actually execute the query.

 

[philhege]

So you want SSMS to behave like an end-user front end?

Try this approach: Create an SP with your statement and the date as a parameter.

Open Excel and go to the Data tab. Create a connection to SQL and select any old table. In the Connection Properties, replace the table type with a script or SP. Enter your stored procedure call with a question mark where the parameter should go. When you open the spreadsheet or refresh the data, Excel will provide a dialog box for you to enter the paramter value.

(Note: this is a quick and dirty explanation. Google "Excel ODC" for more info.)

-----------
With business clients like mine, you'd be better off herding cats.

 

[TamarGranor]

If you want to be prompted for a parameter in SSMS, find your SP in the Object Explorer pane, right-click on it, and choose "Execute Stored Procedure".

Tamar