SQL 2012 - "Choice" option when running a query?

In a nutshell...

I select a bunch of rows using " With X as..."
part of the query that follows displays the selected rows using "Select * from X... "
other code will modify the same rows using "Update X ...  ".
... and I comment out the section I don't want to run. Purely amateur work I know.

Is there a way to use the same "WITH X as... "  query and have it pause and present an option to me where it asks if I want to view or modify the resulting rows? It would then jump to the relevant "subroutine".  I could figure this out in VBA but not here.

It would be ideal to create a little desktop app for this purpose but that's for another day. For now this is all in SSMS.
LVL 1
Salad-DodgerAsked:
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.

Evan CutlerVolunteer Chief Information OfficerCommented:
Im sorry, but it would surprise me to find an example where SMSS would ask for user input DURING an execution.  SMSS was not designed to be a user entry tool.

WHat I usually do is add a column in a table for update triggers, either binary or date, depending on what you like.

After the update, I would add "Select * from <table> where trigger=<setting>"  which would show me the updated set.

if you use date, use today (assuming that this is a once-a-day run).

If you use binary, you can use trigger=true, then run insert a "update <table> set trigger = false" line in your script at the top to reset the triggers prior to the run.

Hope this helps.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Nope.  SQL Server does not have any user interface capabilities such that it can pause execution, prompt for a value, then use that value in future code.    

The only way you can really pull that off is to create a report with more than one dataset:  one that's a 'combo box' like list for the user to select a value (or let them type a free-form value in a textbox, your call), then the main SP that takes that value as a parameter, executes using that value to filter the return set, then displays it to the user.

If you can provide more details, we can give a more detailed answer.

> I could figure this out in VBA but not here.
Same concept, or something like this using Excel VBA.
0
Salad-DodgerAuthor Commented:
That would show the changes that have been made right? I need to see where the proposed changes will go.
0
Ultimate Tool Kit for Technology Solution Provider

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 now.

Evan CutlerVolunteer Chief Information OfficerCommented:
right. Either that or you load your table into a form.
You can use Access to tie into your sql server and create a form from there, and parse through each record.

However, for the most part, scripts in SMSS are written for SMSS.....headless.
0
Salad-DodgerAuthor Commented:
Here's what I have so far. Should be evident what I'm trying to do. I comment out sections I don't need at each moment, I would just like to make it more foolproof.


Use JunkData
	Declare @StDate DateTime
	Declare @EndDate DateTime
	Declare @Factor real
	Declare @TS VarCHar(19)
	Set @TS = Convert(Varchar(19),GetDate(),10)
	Set @Factor = 1.8  ---- How Much Difference to adjust
	Set @StDate = '6/1/2015 00:00:00.000' ------ SET BEGIN DATE
	Set @EndDate = DateAdd(month,1,@StDate); -- One Month
	--SET @EndDate =  DateAdd(day,1,@StDate); --One Day

with X as                      -- "X" becomes a "table view" that will be referenced in subsequent code. 
	(select dateadd(month,(datediff(month,0,TIMEandDATE)),0) as ReadingDate, TimeandDate,Value, id, tagid, _COMMENT,
	Lag(value) over( partition by tagid order by id) preValue,
	Lead(value) over(partition by tagid order by id) postValue
	 from JunkTesting
		Where TagID = 3004
		and TimeAndDate >= @StDate
		and TimeAndDate < @EndDate
	)
	
-------------------------------------------------------------------------------------------------------------------------------
-- Display Proposed Changes:
-- use script below to locate the values that are WAY greater then the average between pre and post value: 
-------------------------------------------------------------------------------------------------------------------------------
--/**
	select * from X
	where 
		coalesce( preValue, 0) <> 0  and
		coalesce( postValue, 0) <> 0 and
		((preValue + postValue) / 2.0) * @Factor < value;
--**/
------------------------------------------------------------------------------------------------------------------------------
--Adjust the coefficient @Factor to  catch all possible incorrect values.
--Then run the update command below to fix the database (use the same coefficient):
-------------------------------------------------------------------------------------------------------------------------------
/**
 UPDATE X
	set value = (preValue + postValue) / 2.0,
	_COMMENT = LTRIM(ISNULL(_COMMENT,''+'Orig Value '+ CAST(Value AS VARCHAR(20)+ ' '+@TS))) -- Places old value into comment field
	WHERE
		coalesce( preValue, 0) <> 0  and
		coalesce( postValue, 0) <> 0 and
		((preValue + postValue) / 2.0) * @Factor < value;
**/
-------------------------------------------------------------------------------------------------------------------------------
-- Display Changes Made
-------------------------------------------------------------------------------------------------------------------------------
SELECT *
  FROM [dbo].[JunkTesting]
  Where TimeandDate > @stDate and TimeandDate < @endDate
  and TagID = 3004
  and _COMMENT is not null
  Order by TimeandDate
GO

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you think about creating a stored procedure that receive parameters and perform depending on the parameters value?
0
Salad-DodgerAuthor Commented:
Would a stored procedure behave differently than a script in the SSMS window? Can you show me an example how this decision code would be done?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry for the delay.
You can build something like:
CREATE PROCEDURE MySP_Name @Param1 CHAR(1)
AS

IF @Param1 = 'V' -- View
        select * from X
	where 
		coalesce( preValue, 0) <> 0  and
		coalesce( postValue, 0) <> 0 and
		((preValue + postValue) / 2.0) * @Factor < value;
ELSE 
    IF @Param1 = 'M'  -- Modify
        UPDATE X
	set value = (preValue + postValue) / 2.0,
	_COMMENT = LTRIM(ISNULL(_COMMENT,''+'Orig Value '+ CAST(Value AS VARCHAR(20)+ ' '+@TS))) -- Places old value into comment field
	WHERE
		coalesce( preValue, 0) <> 0  and
		coalesce( postValue, 0) <> 0 and
		((preValue + postValue) / 2.0) * @Factor < value;
    ELSE 
        PRINT 'Wrong parameter value'

GO

Open in new window

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
Salad-DodgerAuthor Commented:
That looks like just what I wanted. Perhaps this is a silly question (and I will test this today) but does this have to be a Stored Procedure for this to work? Just putting this into a script would not work the same way?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, but then you'll need to edit the script every time you want to change the behaviour:
DECLARE @Param1 CHAR(1)='V' --> Change this value if you want to perform another operation

IF @Param1 = 'V' -- View
        select * from X
	where 
		coalesce( preValue, 0) <> 0  and
		coalesce( postValue, 0) <> 0 and
		((preValue + postValue) / 2.0) * @Factor < value;
ELSE 
    IF @Param1 = 'M'  -- Modify
        UPDATE X
	set value = (preValue + postValue) / 2.0,
	_COMMENT = LTRIM(ISNULL(_COMMENT,''+'Orig Value '+ CAST(Value AS VARCHAR(20)+ ' '+@TS))) -- Places old value into comment field
	WHERE
		coalesce( preValue, 0) <> 0  and
		coalesce( postValue, 0) <> 0 and
		((preValue + postValue) / 2.0) * @Factor < value;
    ELSE 
        PRINT 'Wrong parameter value'

GO

Open in new window

0
Salad-DodgerAuthor Commented:
OK, Copy that. Thank you.
0
Salad-DodgerAuthor Commented:
Thanks for the help, sorry it was left open so long.
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.