I want to use the same value for different queries from different databases

DECLARE @GLOBAL_VAR_1 INT = Value_1 DECLARE @GLOBAL_VAR_2 INT = Value_2 USE "DB_1" GO SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_1 AND "COL_2" = @GLOBAL_VAR_2 USE "DB_2" GO SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_2

but it gives an error.

You must declare a scalar variable "@GLOBAL_VAR_2".

Can anyone suggest any way to do this...?

11 answers

It is not possible to declare a global variable in Transact-SQL. However, if all you want is for your variables to be available to batches of a single script, you can use the tool SQLCMD or SQLCMD mode SSMS and define this tool/mode dependent variables, for example:

:setvar myvar 10

And then use them like this:

$(myvar)

To use SSQ mode of SQLCMD:

You you can not declare global variables in SQLServer.

If you are using Management Studio you can use SQLCMD mode as mentioned by @Lanorkin.

Otherwise, you can use CONTEXT_INFO to store one variable that is visible during the session and connection, but it will disappear after that.

The only truly global way would be to create a global temporary table (named ##yourTableName) and store your variables there, but this will also disappear when all connections are closed.

You can try a global table:

Create table ##global_var (var1 int ,var2 int) USE "DB_1" GO SELECT * FROM "TABLE" WHERE "COL_!" = (select var1 from ##global_var) AND "COL_2" = @GLOBAL_VAR_2 USE "DB_2" GO SELECT * FROM "TABLE" WHERE "COL_!" = (select var2 from ##global_var)

In this particular example, the error is related to the GO after the use statements. GO statements reset the environment so there are no user variables. They must be announced again. And the answer to the question about global variables is No, there are no global variables, at least Sql server versions equal to or before 2008. I cannot guarantee the same for newer versions of SQL Server.

Best regards, HINI

Starting in SQL Server 2016, a new way to share information in a session is introduced through SESSION_CONTEXT and sp_set_session_context .

You can use them as an alternative to CONTEXT_INFO() , which only stores the binary value, limited to 128 bytes. Also, the user can overwrite the value at any time, and it is not a good idea to use it for security testing.

The following problems are resolved using new utilities. You can store data in a more user-friendly format:

EXEC sp_set_session_context "language", "English"; SELECT SESSION_CONTEXT(N"language");

Additionally, we can mark it as read-only:

EXEC sp_set_session_context "user_id", 4, @read_only = 1;

If you try to change the read-only session context you will get something like this:

Msg 15664, Level 16, State 1, sp_set_session_context, Line 10 The key 'user_id' cannot be set in the session context. The key was installed as soon as possible for this session.

Try using; instead of GO. It worked for me on 2008 R2 version

DECLARE @GLOBAL_VAR_1 INT = Value_1; DECLARE @GLOBAL_VAR_2 INT = Value_2; USE "DB_1"; SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_1 AND "COL_2" = @GLOBAL_VAR_2; USE "DB_2"; SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_2;

A similar result can be achieved by creating scalar functions that return the values ​​of variables. Of course, function calls can be expensive if you use them in queries that return a large number of results, but if you limit the result set you should be fine. Here I'm using a database created just to store these semi-static values, but you can also create them on a per-database basis. As you can see, there are no input variables here, just a well-named function that returns a static value: if you change that value in the function, it will instantly change wherever it is used (the next time it is called). >

USE GO CREATE FUNCTION dbo.global_GetStandardFonts () RETURNS NVARCHAR(255) AS BEGIN RETURN "font-family:"Calibri Light","sans-serif";" END GO -- Usage: SELECT " ..." -- Result: ...

Before a variable or constant can be used in a program, it almost always needs to be declared. All announcements must be placed in the announcements section of the PL/SQL program. In PL/SQL, declarations can refer to variables, constants, TYPE (such as collections or records), and exceptions. This article covers variable and constant declarations.

Declaring a PL/SQL Variable

When you declare a variable, PL/SQL allocates memory to hold its value and gives the allocated memory area a name by which the value can be retrieved and modified. The declaration also specifies the data type of the variable; it is used to check the values ​​assigned to it. The basic syntax for declaring a variable or constant is:

Name datatype [ := | DEFAULT default_value]; BEGIN FOR book_rec IN (SELECT * FROM book) LOOP process_book (book_rec); END LOOP; END;

Benefits of Linked Ads

In all declarations, the type of the variable (character, numeric, logical, etc.) is specified explicitly. Each declaration directly specifies the data type and, usually, the constraint imposed by the purpose of that type. This is a common approach to declaring variables, but it can cause problems in some situations.

• Synchronization with database columns. A PL/SQL variable often "represents" information from a database table. If you explicitly declare a variable and then change the table structure, it can break your program.
• Normalization of local variables. Let's say a PL/SQL variable stores calculated values ​​that are used in different places in the application. What are the consequences of repeating (hard-coding) the same data types and constraints across all declarations?

The SET statement assigns a value to a run-time variable. These variables can be platform-specific system variables or user variables.

Keywords

variable

Indicates a system or user variable.

meaning

Indicates a string or numeric value corresponding to a system or user variable.

General rules

Variable values ​​are set for the duration of the session. The values ​​assigned to a variable must match the data type of that variable. For example, you cannot assign a string value to a variable that is declared as a numeric data type. The command that creates the variable varies across platforms. For example, DB2, Oracle, and SQL Server use the DECLARE statement to declare the name and type of a variable, but other platforms may use different ways to create variables.

The value that is assigned to a variable does not have to be a constant. This can be a dynamically generated value created based on a subquery. For example, we can assign the variable emp_id_var the maximum value of the employee id (emp_id)|.

DECLARE emp_icLvar CHAR(5) SET empty var=(SELECT MAX(emp_id) FROM employees WHERE type="F")

In this example, type F means that the employee works full-time and is salaried.

The SET instruction is very easy to port from platform to platform. Only Oracle uses a slightly different scheme for assigning a variable value. In the following example, we will declare a variable in SQL Server called emp_id_var and assign a value to it.

DECLARE emp_id var CHAR(5) SET emp_id_var="67888";

Now we will perform the same operation for the Oracle server.

DECLARE emp_id_var CHAR(5); emp_id_var:= "67888";

DB2

The DB2 platform supports a basic form of SET statement for assigning values ​​to local variables, output parameters, or special registers. You can assign multiple values ​​at once in a single SET statement. This statement also allows you to assign values ​​to base table columns in a trigger. You cannot assign values ​​to two types of variables in one statement.

SET variable=(value | NULL | DEFAULT) [, …]

The syntax elements in DB2 are as follows.

variable

The target variable is specified. SQL variables must be declared before they can be used. The variable can also represent a column in the trigger's base table.

meaning

The value of the variable is specified according to its data type. When assigning values ​​to columns in a trigger, you can also use correlation name references OLD and NEW. For more information on this, see the DB2 subsection of the CREATE/ALTER TRIGGER Statement section.

A column and variable that can accept NULL values ​​are assigned the value NULL.

Columns that were created using the WITH DEFAULT or IDENTITY clause are assigned a default value. This clause also assigns NULL to those columns that accept null values ​​and are not defined with DEFAULT and IDENTITY clauses. Let's assign a value to one variable.

SET new var.order_qty=125;

If you assign values ​​to multiple variables in one statement, the number of variables to the left of the equal sign must exactly match the number of values ​​to the right. Let's assign values ​​to several variables.

SET new_var. order_qty=125, new_.va discount =

When using an option such as SET variable=SELECT, the result set values ​​of the SELECT statement must exactly match the variables in number, position, and data type. If the SELECT statement does not return any values, then the variables are assigned NULL values. You can also use the SELECT...INTO statement to assign values ​​to multiple variables in one statement.

MySQL

The SET keyword has several uses in MySQL. First, SET is a MySQL data type that can have multiple values ​​separated by commas. (For information about this application, see Chapter 2, MySQL Data Types.) In addition, the SET statement can assign values ​​to a user-defined variable. This method of application is described here. The syntax is as follows:

SET variable - value […]

When assigning values ​​to several variables in one instruction, these values ​​are separated from each other by commas.

SET new_var.order_.aty - 125. new_var. discount - 4;

Additionally, MySQL allows you to use the SELECT statement to assign values ​​to variables, just as described in the ANSI section. However, the SELECT statement method has several weaknesses. The main problem is that values ​​are not assigned to SELECT statements immediately. So in the following example:

SELECT (_>new_var - row_id) AS a, (@new_var + 3) AS b FROM Lable_name;

the @new_var variable will not receive a new value row id + 3. The value it had at the beginning of the instruction will be retained. Therefore, it is good practice to assign only one value to a variable at a time.

Oracle

The SET clause is not supported as a method for assigning values ​​to variables in Oracle. Instead, user variables are assigned values ​​using the assignment operator:=. The basic syntax is as follows.

PostgreSQL

In PostgreSQL, the SET command is used to assign a value to a variable at runtime.

SET variable(THO | -) (value | DEFAULT)

A variable can be assigned a constant string value at runtime. When you use the DEFAULT keyword, the run-time variable is set to its default value. The PostgreSQL 7.2 platform supports the following variables.

CLIENT ENCODING NAMES

Sets the multibyte encoding for PostgreSQL client systems built with multibyte support.

DATESTYLE

Sets the style used to display the date and time. The following styles are supported.

Date and time are displayed in GPT-MM-DD HH:MM:SS format (default ISO 8601 style).

Date and time are displayed in Oracle/Ingres style rather than the style required by the ANSI SQL standard.

PostgreSQL

Date and time are displayed in PostgreSQL long format, but are no longer than the default.

The date and time are displayed as DD.MM.YYYY. You can refine SQL and Postgresql styles by using the European, US, and NonEuropean keywords, which give dates the formats dd/mm/yyyy, mm/dd/yyyy, and mm/dd/yyyy, respectively. For example: SETDATESTYLE=SQL, European.

Sets the initial value for the internal random number generator. The value can be any floating point number between 0 and 1 multiplied by 231-1. This value can also be set using the PostgreSQL setseed function. For example:

SELECT setseed(value); SEVER ENCODING;

Sets multi-byte encoding for servers built with multi-byte support.

Below is an example of setting the Oracle and European style date and time format.

SET DATESTYLE TO sql, European;

SOL Server

The SQL Server platform supports assigning values ​​to variables using the SET statement if the variables were previously created using the DECLARE statement, as well as assigning values ​​to cursor variables. (SQL Server also uses the SET statement for other purposes, such as to enable or disable session flags with commands like SETNOCOUNT ON.) Platform-specific syntax is provided below.

This statement does not support the DEFAULT keyword, but otherwise supports ANSI syntax. The server_name value must refer to the connection specified in the previous CONNECT statement, either in constant or variable form.

Setting Variable Values

Currently, the SQL language provides two ways to set the value of a variable - you can use the SELECT or SET statement for this purpose. In terms of functionality, these statements operate almost identically, except that the SELECT statement allows you to obtain the original assignment value from the table specified in the SELECT statement.

The SET statement is typically used to set the values ​​of variables in a form more common in procedural languages. Typical examples of the use of this operator include the following:

SET @b = @a * 1.5

Note that all of these statements perform direct assignment operations, either using explicit values ​​or other variables. You cannot use the SET statement to assign a value to a variable that is retrieved by a query; the query must be executed separately and only after that the resulting result can be assigned using the SET statement. For example, an attempt to execute such a statement causes an error:

SET @c = COUNT(*) FROM City

and the following statement executes quite successfully:

SET @c = (SELECT COUNT(*) FROM City)

The SELECT statement is typically used to assign values ​​to variables when the source of the information to be stored in the variable is a query. For example, the actions performed in the code above are much more commonly implemented using a SELECT statement:

SELECT @c = COUNT(*) FROM City

Note that this code is a little clearer (in particular, it is more concise, although it does the same things).

Thus, it is possible to formulate the following generally accepted convention for the use of both operators.

The SET statement is used when a simple variable assignment operation is to be performed, i.e. if the value being assigned is already given explicitly in the form of a specific value or in the form of some other variable.

The SELECT statement is used when the assignment of a value to a variable must be based on a query.

Using Variables in SQL Queries

One of the useful properties of T-SQL is that variables can be used in queries without the need to create complex dynamic strings that embed variables in the program code. Dynamic SQL continues to exist, but a single value can be changed more easily - using a variable.

Wherever an expression can be used in a query, a variable can also be used. The following example demonstrates the use of a variable in a WHERE clause:

DECLARE @IdProd int;

SET @IdProd = 1;

SELECT

Microsoft SQL Server has a special data type TABLE, based on which we can create table variables, in order to use them in your instructions and procedures, and today we will look at these variables, find out how they are declared and what features these variables have.

Description of MS SQL Server table variables

Table Variables are variables with a special TABLE data type that are used to temporarily store the resulting set of data in the form of table rows. They appeared back in the 2005 version of SQL server. You can use such variables in stored procedures, functions, triggers, and regular SQL packages. Table variables are created in the same way as regular variables by declaring them with the DECLARE statement.

Variables of this type are intended as an alternative to temporary tables. If we talk about whether it is better to use table variables or temporary tables, then there is no clear answer; table variables have both pros and cons. For example, I personally like to use table variables because they are convenient to create ( those. announce) and there is no need to think about removing them or clearing them at the end of the instructions, since they are automatically cleared ( just like regular variables). But at the same time, it is better to use table variables only when you are going to store a small amount of data in them, otherwise it is recommended to use temporary tables.

Benefits of Table Variables in Microsoft SQL Server

• Table variables behave like local variables. They have a precisely defined field of application;
• Table variables are automatically cleared at the end of the statement where they were defined;
• When using table variables in stored procedures, recompilations occur less frequently than when using temporary tables;
• Transactions using TABLE variables continue only while the corresponding variable is being updated. Due to this, table variables are less likely to be locked and require fewer resources to maintain logs.

Disadvantages of Table Variables in MS SQL Server

• Queries that modify TABLE variables do not create parallel query plans;
• TABLE variables do not have distribution statistics and do not trigger recompilations, so it is recommended to use them for a small number of rows;
• Table variables cannot be modified once they are created;
• Table variables cannot be created using a SELECT INTO statement;
• TABLE variables do not change during transaction rollbacks because they have a limited scope and are not part of persistent databases.

Examples of using table variables in Microsoft SQL Server

Now let's move on to practice, and first I would like to note that my server is Microsoft SQL Server 2016 Express, in other words, all the queries below were run on this version of the DBMS.

First, let's create a test table and populate it with test data to see how table variables can be used with regular tables.

CREATE TABLE TestTable(ProductId INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(50) NULL CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ProductId ASC)) GO INSERT INTO TestTable (ProductName) VALUES ("Computer"), ("Monitor") , ("Printer") GO SELECT * FROM TestTable

I used the CREATE TABLE statement to create the TestTable table, then I used the INSERT statement in conjunction with the VALUES table value constructor to add data to the table, then I used the SELECT statement to select from the newly created table.

Declaring a table variable and using it

In this example, we will declare a table variable, add data to it, and make a selection from two tables ( table variable and regular table) with union.

Declaring a table variable DECLARE @TableVar TABLE(ProductId INT NOT NULL, Price MONEY NULL); --Adding data to a table variable INSERT INTO @TableVar (ProductId, Price) VALUES (1, 500), (2, 300), (3, 200) --Using a table variable with data join SELECT TTable.ProductId, TTable.ProductName , TVar.Price FROM @TableVar TVar LEFT JOIN TestTable TTable ON TVar.ProductId = TTable.ProductId

Creating a table variable with a primary key, a UNIQUE constraint, and a nonclustered index

This example shows how you can create a primary key, UNIQUE constraints, and nonclustered indexes on table variables. The ability to create a non-clustered index has been available since Microsoft SQL Server 2014.

Table variable declaration DECLARE @TableVar TABLE(ProductId INT NOT NULL PRIMARY KEY, --Primary key ProductName VARCHAR(50) NOT NULL, Price MONEY NOT NULL, UNIQUE (ProductName, Price), --Constraint INDEX IX_TableVar NONCLUSTERED (Price) -- Non-clustered index); --Adding data to a table variable INSERT INTO @TableVar (ProductId, ProductName, Price) VALUES (1, "Computer", 500), (2, "Monitor", 300), (3, "Printer", 200); --Data selection SELECT ProductName FROM @TableVar WHERE Price > 200

This concludes my story about table variables, if you want to study the T-SQL language in detail, I recommend reading my book “The T-SQL Programmer’s Way”, I hope the material was useful to you, bye!