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
)
This question is related to
sql-server-2005
stored-procedures
select
select @currentTerm = CurrentTerm, @termID = TermID, @endDate = EndDate
from table1
where IsCurrent = 1
One advantage your current approach does have is that it will raise an error if multiple rows are returned by the predicate. To reproduce that you can use.
SELECT @currentTerm = currentterm,
@termID = termid,
@endDate = enddate
FROM table1
WHERE iscurrent = 1
IF( @@ROWCOUNT <> 1 )
BEGIN
RAISERROR ('Unexpected number of matching rows',
16,
1)
RETURN
END
Source: Stackoverflow.com