Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Global Variable Expand / Collapse
Author
Message
Posted Tuesday, July 30, 2013 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 11:40 AM
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()))))

Post #1479097
Posted Tuesday, July 30, 2013 11:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1479113
Posted Tuesday, July 30, 2013 11:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 42,764, Visits: 35,858
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 2008, MVP
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

Post #1479114
Posted Tuesday, July 30, 2013 11:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1479119
Posted Tuesday, July 30, 2013 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 11:40 AM
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.
Post #1479124
Posted Tuesday, July 30, 2013 12:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:03 PM
Points: 3,486, Visits: 7,531
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1479130
Posted Tuesday, July 30, 2013 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 11:40 AM
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!
Post #1479134
Posted Tuesday, July 30, 2013 4:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:19 PM
Points: 800, Visits: 713
You could use a single row temp table for your "global variables".

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1479213
Posted Wednesday, July 31, 2013 12:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 AM
Points: 5,029, Visits: 11,761
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1479290
Posted Wednesday, July 31, 2013 1:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:19 PM
Points: 800, Visits: 713
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
Post #1479320
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse