Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38

    Question Unanswered: Global Variables

    Hi,

    Is there a way to declare a persistent global variable in SQL Server?

    I'd like my stored procs to fetch data in a different source depending on a debug (or development) variable.

    For example, I'd like to be able to set a variable to either 0 or 1 (true or false) and have a static SP defined as:


    IF @MYVARIABLE = 1
    SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
    ELSE
    SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')


    What do you think? Since these SPs should be called a lot, I don't want to store the info in a table, I want it as a global variableso it will be as fast as possible.

    Any other suggestions are also welcomed.

    Thanks,

    Skip.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Wouldn't you just put this into a table? Then add the statement:

    Code:
    DECLARE
      @Environment bit
    
    SELECT @Environment = Environment FROM tblFlags
    
    IF @Environment = 1
    SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
    ELSE
    SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')
    But wouldn't it be better to have a dedicated test server with data refreshed as you require? Sorry, may not always be realistic, but just a thought.

    Regards,

    hmscott

  3. #3
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38
    First of all, yes having a dev server makes way more sense. Actually, this is what we have here, the switch (the if...else) will be used to create the linked server to point to the correct server instance. The example above was only a reference and did not represent an actual situation.

    Second, what I have now is a variable in a "commands" table. It works fine but the function is called so many times that it slows my system. I'd like to have a global variable in memory to speed up the process of reading its value.

    Any other suggestions?

    Thanks,

    Skip.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Nothing comes to mind. You can use the PINTABLE command to put that table into memory, but if it is called so frequently, it's probably already there.

    You said you have a "commands" table, but that the function (what function?) is called so often that it slows the system. Just to check, is the table very large? Is it properly indexed? What do you consider to be heavy usage?

    We have a particular sp that is called more or less each time a user hits a page on our site (to pull back configuration and web settings). We have about 300 users and about 2000 page hits per day (frequently more). I've never had any complaints about this particular sp running slowly.

    Regards,

    hmscott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •