Menu
I have a stored procedure in SQL Server 2005 with multiple variables and I want to set the values of these variables using a select statement. All three variables come from a same table and there should be a way to set them using one select statement instead of the way I currently have as shown below. Please help me to figure it out.
DECLARE @currentTerm nvarchar(max) DECLARE @termID int DECLARE @endDate datetime SET @currentTerm = ( Select CurrentTerm from table1 where IsCurrent = 1 ) SET @termID = ( Select TermID from table1 where IsCurrent = 1 ) SET @endDate = ( Select EndDate from table1 where IsCurrent = 1 ) 
710
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
SET and SELECT may be used to assign values to variables through T-SQL. Both fulfill the task, but in some scenarios unexpected results may be produced. In this tip I elaborate on the considerations for choosing between the SET and SELECT methods for assigning a value to variable.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
select @currentTerm = CurrentTerm, @termID = TermID, @endDate = EndDate from table1 where IsCurrent = 1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
