MS SQL 2012 User Entered Data in Query

At the moment, I'm using a Find and Replace in the query to put the latest date in.

Do you mean you are using SSMS for record edition?

Can you share the query with us? Maybe we can suggest you a simple query to do the job.

Hi Vitor

I've attached a text file with one of the queries in it.  At the moment, I would have to replace 2014/11/21 with 2014/11/28 for the following week.

I hope this makes sense.  I haven't notated the file yet as it's pretty new and I had to get it written quickly.

Thanks
Sarah
Rec-Match.txt

Declare and set in the beginning of the script a variable to hold Today's date:

DECLARE @Today AS DATE
SET @Today = CAST(GETDATE() AS DATE)

Select allOpen in new window

Then replace in all script '2014/11/21' for @Today, so you can run it in any day.

How do you define user in this context? How does the user execute this query? Have you considered to store the value in a table?

btw, why those ALTER TABLE statements on permanent tables? Consider using temporary tables or table variables. Otherwise you may get problems due to schema locks.

Hi Both

Vitor - thanks, but that only gives me today's date.  I might not be running the query on the week ending date, e.g. I won't get this week's data until Monday, but the week ending date will be 2014/11/27.  That's why I need to input it somehow :-(.

Ste5an - the user, at the moment, will be me, but might be someone else in the future.  I run the queries from the Solution Explorer.  Some of them are very long at the moment and I sometimes have to break into them and carry them on the next day, so it suits me to do it this way.  I'm sure there are more efficient ways I could do it, but I'm still learning!  It's only part of my job and I'm self-taught, so I tend to have to learn as I go.

Thanks
Sarah

What do you mean? You need a week period and not a single day?
If so, here's one possible solution:

DECLARE @WeekBegin AS DATE
DECLARE @WeekEnd AS DATE
SET @WeekEnd = CAST(GETDATE()+1 AS DATE)
SET @WeekBegin = @WeekEnd-7

Select allOpen in new window

Then, in the WHERE clauses just change it to:

WHERE Rec_Date BETWEEN @WeekBegin AND @WeekEnd

Select allOpen in new window

Hi Vitor

No, I do need a single date, but I can't say in advance what that date might be.  It could be any date as it refers to when table sources were last updated.  Ross, here, is a download from a general ledger.  For accounting purposes, I need to mark the matched records according to the date the ledger was last updated, which is normal in accounting.

I hope that explains it a bit better :-).

Thanks
Sarah

Ok. Tell me how to get that data so I show you how to store it in the variable.
For now you have a solution to change it only in one place:

DECLARE @LastUpdated AS DATE
SET @@LastUpdated = '2014/11/21'

Select allOpen in new window

Then replace in all script '2014/11/21' for @LastUpdated. Each time you need to run it just come to this section and update the date.

Hi Vitor

That makes sense in that I only have to change once per query and I will do that if I can't find a way to key it in, but I'd really like to key it in somehow if I can, firstly because it happens in more than one query so I'll still have to change it a number of times and secondly because it would be really useful to know how to enter this kind of data in.

Thanks
Sarah

It could be any date as it refers to when table sources were last updated.

So, how do you know which date to use?

Hi Vitor

I know from when I phsyically go and download the ledger.  I have date parameters I can set in the download procedure (using SAS Enterprise Guide).

Thanks
Sarah

Just want to find a way for automation of this process.

Hi Vitor

I don't really think it can be automated as the date could vary so much, plus it would be really useful to know how to get data into queries in this way.

Thanks
Sarah

Everything can be automated with more or less complexity. Anyway, I thought that was the reason why you opened this question. Sorry if I misunderstood it.

Hi Vitor

I want to be able to type a date somewhere and have SQL update the query with that date.  Does that make more sense?  Do you think you can help?  Thanks for trying, if not :-)

Thanks
Sarah

You can do it directly in the script as I showed you or you can build a table with a single date column and let users update that table and then let your script read the date from that table:

DECLARE @LastUpdated AS DATE
SELECT @LastUpdated = MAX(LastUpdateDate) FROM LastUpdateTable

Select allOpen in new window

Hi Vitor

Updating from another table would probably be the way for me to go as I can put whatever date I like there.  I will try it and see how it goes.

Thanks for your help.

Sarah

Hi Vitor

I tried this and have an error.  My simple code to try it out is:

USE SLC_Inc;
--
--
DECLARE @LastUpdated AS DATE
SELECT @LastUpdated = MAX(LastUpdateDate) FROM LastUpdateTable
--
SELECT *
INTO SSNs_Temp
FROM [dbo].[SLC_1213_SSN];
--
--
ALTER TABLE SSNs_Temp ADD MYDATE smalldatetime default '2014/10/27';
GO
--
UPDATE SSNs_Temp SET MYDATE = @LastUpdated;
--

I got the following error:

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@LastUpdated".

Does this mean that I've declared the variable to early in the code?  This sort of thing drives me mad! :-)

Thanks
Sarah

Hi Ste5an

Bums!  I don't know how to add a column in any other way.  I suppose I could add all the columns in and then declare the variable, but what if you have to manipulate some data in table using the variable, then use that table to create another table, then manipulate the new one in the same way?  Would you have to keep declaring the variable again?

Thanks
Sarah

What SQL Server version are you using? How many rows are involved? Can you post table DDL for your involved tables?

Hi Both

Vitor - that works perfectly, thank you.

Ste5an - thanks for your help.  I seem to have a solution I can live with :-).

I'm going to award most of the points to Vitor as he came up with the solution, but some to Ste5an as he's helped and has made some useful points.

Hope that's OK with both of you.

One day, I'd still like to figure out if it's possible to use something similar to an input box though as I can think of loads of times this would be useful.

Thanks
Sarah