Use them in the WHERE clause of a SELECT statement. The following batch, for example, declares two variables, assigns values to them, and then uses them in the WHERE clause of a SELECT statement: USE AdventureWorks2014 This is the preferred method of assigning a value to a variable. To assign a variable a value by using the SET statement, include the variable name and the value to assign to the variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement. ![]() To assign a value to a variable, use the SET statement. When a variable is first declared, its value is set to NULL. Setting a Value in a Transact-SQL Variable DECLARE INT ĮXECUTE sp_executesql N'SELECT - this produces an error In the following example, the nested scope created for execution of sp_executesql does not have access to the variable declared in the higher scope and returns and error. WHERE BusinessEntityID = have local scope and are only visible within the batch or procedure where they are defined. no longer legal to reference BusinessEntityID, NationalIDNumber, JobTitle This SELECT statement generates a syntax error because it is ![]() has gone out of scope and no longer exists. Terminate the batch by using the GO keyword. For example, the following script generates a syntax error because the variable is declared in one batch and referenced in another: USE AdventureWorks2014 The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. The scope of a variable is the range of Transact-SQL statements that can reference the variable. To declare more than one local variable, use a comma after the first local variable defined, and then specify the next local variable name and data type.įor example, the following DECLARE statement creates three local variables named and and initializes each to NULL: DECLARE NVARCHAR(30), NVARCHAR(20), NCHAR(2) For variables of type XML, an optional schema collection may be assigned.įor example, the following DECLARE statement creates a local variable named with an int data type. For numeric variables, a precision and scale are also assigned.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |