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.
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.
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 🙂
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 16 total)
You must be logged in to reply to this topic. Login to reply