Assigning a value to SQL variable:
Declare @date Datetime
set @date = getdate()
We can achieve the same using simple select statement:
select @date = getdate()
If we try to do the same from a result of query, then it will be
select @date = DateCreated from SomeTable where PrimaryKey = SomeValue
We are sure that this query will return only one record, so this statement is valid and always returns expected result. In a scenario, where the result is more than one then which value will be assigned to that variable. Will it be first record or last record or anything in between?
declare @empid char(8)
select top 10 @empid = empid from Empmaster where DeptID = 'A' order by empid
select top 10 * from Empmaster where DeptID = 'A' order by empid
It will always get assigned to the last result data. So to be on the safe side we need to put the order by clause and use the appropriate order as ascending or desending considering the fact it always pull the last record. Otherwise we need to use the “Top 1” so it always use the value of first record in the result with proper sorting.