July 30, 2013 at 10:18 am
Hi, I would like to create a global variable that I can reference multiple times and here is the script. it runs fine, but the problem is it gives me an error when I run a batch separately that references this variable. Is there a way to keep this variable alive so this can be referenced outside its own batch?
declare @@BeginDate smalldatetime
set @@BeginDate = convert (smalldatetime, '1/1/' + convert(varchar,year(dateadd(month,-1,getdate()))))
July 30, 2013 at 11:22 am
Broccoli (7/30/2013)
Hi, I would like to create a global variable that I can reference multiple times and here is the script. it runs fine, but the problem is it gives me an error when I run a batch separately that references this variable. Is there a way to keep this variable alive so this can be referenced outside its own batch?
declare @@BeginDate smalldatetimeset @@BeginDate = convert (smalldatetime, '1/1/' + convert(varchar,year(dateadd(month,-1,getdate()))))
There is no such thing as "a Global Variable" in SQL Server. Having two @ doesn't makes local variable to be global. You can use as many @'s as you like, your variable will still be local:
declare @@@@BeginDate smalldatetime
set @@@@BeginDate = convert (smalldatetime, '1/1/' + convert(varchar,year(dateadd(month,-1,getdate()))))
select @@@@BeginDate
July 30, 2013 at 11:22 am
You can't create global variables in SQL Server.
What are you trying to do? Maybe there's an alternative...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2013 at 11:34 am
Yep, there are some alternatives. First what comes into my mind it's a CONTEXT_INFO:
July 30, 2013 at 11:43 am
oh I see, thanks for your reply, you surely did help me learn something new about SQL server.
I wanted to create a variable, which is the first day of the last month. and I have multiple queries that reference this variable. But I guess I would have to set up this variable at the beginning of each batch.
July 30, 2013 at 12:31 pm
There's no need to have a variable for that, simply use a date calculation. 😉
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
July 30, 2013 at 12:41 pm
this is great, thanks much!!
thought I could just create a global variable with it and reference the variable name each time I need that value instead of typing this whole formula every time, but this works too.
thanks!
July 30, 2013 at 4:02 pm
You could use a single row temp table for your "global variables".
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 31, 2013 at 12:00 am
Erland Sommarskog (7/30/2013)
You could use a single row temp table for your "global variables".
Presumably you mean a global temp table.
Although it would probably work, it's a bad idea - it would require just as much code as the DATEADD solution, with the added server overhead of having to retrieve the info from disk.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2013 at 1:34 am
Phil Parkin (7/31/2013)
Presumably you mean a global temp table.
No. Broccoli appears to be talking about a single script, divided into multiple batches. Thus a local temp table would do fine.
Although it would probably work, it's a bad idea - it would require just as much code as the DATEADD solution, with the added server overhead of having to retrieve the info from disk.[/quote]
The logic for the expression would be in a single place. If you have a couple of these suckers, and they are decently complex, the temp table is not a bad idea.
The data would be in cache, but it is true that it would be backed by disk space, so there is some overhead.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 31, 2013 at 1:44 am
If you have a couple of these suckers, and they are decently complex, the temp table is not a bad idea.
On this, I think we shall have to agree to disagree.
From BOL:
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
If this stuff is worth storing in a table and is going to be referenced by multiple scripts or procs, it should be held in a permanent table, not one which may disappear unexpectedly.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2013 at 1:57 am
Phil Parkin (7/31/2013)
If this stuff is worth storing in a table and is going to be referenced by multiple scripts or procs, it should be held in a permanent table, not one which may disappear unexpectedly.
My assumption was that we were talking about a single script, consisting of multiple batches. In which case a local temp table would do.
If we are talking about a multitude of scripts, global variables would not work, even if they existed(*). In that case, maybe the best option would be user-defined functions.
(*) Those who have worked with SQL Server for a long time, know that once there was something called "global variables", and indeed they had leading @@. But they were all pre-defined and readonly, that is @@spid, @procid and the like. Today they are referred to as functions, but you can still use them in context where normal built-in functions cannot be used.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 31, 2013 at 2:16 am
My assumption was that we were talking about a single script, consisting of multiple batches. In which case a local temp table would do
I see! I agree with your comments in that case 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2013 at 3:26 am
(*) Those who have worked with SQL Server for a long time, know that once there was something called "global variables", and indeed they had leading @@. But they were all pre-defined and readonly, that is @@spid, @procid and the like. Today they are referred to as functions, but you can still use them in context where normal built-in functions cannot be used.
Do you mean Sybase? I have worked with SQL server since their rebranded version of Sybase. Some people might called them as they used to in Sybase, but I cannot remember that these ones were ever refered to as "global variables" in MS SQL specs. Well, I could be very wrong, as it was really long-long ago, when I couldn't read in English :-D.
July 31, 2013 at 12:46 pm
Eugene Elutin (7/31/2013)
Do you mean Sybase? I have worked with SQL server since their rebranded version of Sybase. Some people might called them as they used to in Sybase, but I cannot remember that these ones were ever refered to as "global variables" in MS SQL specs.
Here is a screenshot from Books Online for SQL 6.5 to prove it.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply