SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Global Variable


Global Variable

Author
Message
Broccoli
Broccoli
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 23
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()))))


Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5150 Visits: 5478
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 smalldatetime
set @@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



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90117 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5150 Visits: 5478
Yep, there are some alternatives. First what comes into my mind it's a CONTEXT_INFO:

http://msdn.microsoft.com/en-us/library/ms180125.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Broccoli
Broccoli
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 23
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.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17060 Visits: 19122
There's no need to have a variable for that, simply use a date calculation. ;-)


SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Broccoli
Broccoli
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 23
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!
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2167 Visits: 872
You could use a single row temp table for your "global variables".

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19187 Visits: 20461
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2167 Visits: 872
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search