SQL report date being a promt instead of hard code

I have a SQL query where the date is hard coded. I inherited this code and I am not too good at it.

Here is the current code that I need help with....

DECLARE @ReportDate DATE = CONVERT(date,'2013-09-17');

Open in new window


I am using a third party interface that can read SQL. It has the ability to understand prompts. I do not want the report to always run with that date, nor do I want to always change it when it needs to be run......

Here is an example of a code that uses a prompt of a date.

WHERE ((NewBusinessLineItem.Sold='Yes')) AND (([NewBusinessHeader].[Process_Complete_Date])>=<<Enter the From Date[Date]>> AND [NewBusinessHeader].[Process_Complete_Date]<= <<Enter the To Date[Date]>>) AND (((NewBusinessHeader.Dept) In (<<Enter Personal Lines, Commercial Lines, Benefit lines (seperated by a comma for mulitple) [Text]+>>)))

Open in new window


I am unsure how to change the first code to act as a prompt. Don't want to mess it up.
DECLARE @ReportDate DATE = CONVERT(date,'2013-09-17');

Open in new window

Michael FranzCFOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
It seems that your application is hardcoded for 15 days ago.

There should be a configuration or settings file that drives this.

before you make any changes, be sure you back up any files you change.
Be sure you observe any change windows, downstream processes, etc. - I know you know this, but have to be careful.
And proceed carefully..

Hope this helps.
0
Michael FranzCFOAuthor Commented:
How do I change the syntax

DECLARE @ReportDate DATE = CONVERT(date,'2013-09-17');

to act as a prompt like the other one??


What is meant by :There should be a configuration or settings file that drives this.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I am unsure how to change the first code to act as a prompt.
SQL Server is a database, not a UI tool, so it does not have the ability to prompt users for anything, and delay execution until the user makes a selection.

SSRS however allows for prompting of parameters, to include default values and using a table/view/SP as the list of values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Michael FranzCFOAuthor Commented:
I've requested that this question be deleted for the following reason:

For the first time, not getting a solution. Someone else help me with the solution
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I object to the closing of the question, as 'You can't prompt the user for a value in SQL Server' is the correct answer.
0
Anthony PerkinsCommented:
Re-opening the question as either the author needs to post their solution or they should accept that it cannot be done using T-SQL. In other words The experts told me “you can’t do that”. What do I do now? is a valid answer.
0
Anthony PerkinsCommented:
3) http:#a39546685
As indicated in that comment there is no way to prompt for a parameter with T-SQL or even SSMS.  That is not to say it cannot be done with some other application such as SSRS for example.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.