Global Variable

  • 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()))))

  • 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[/url]

  • 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
  • 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[/url]

  • 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.

  • 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
  • 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!

  • 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]

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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]

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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]

  • 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 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • (*) 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.

    _____________________________________________
    "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[/url]

  • 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